Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
154 Practical PowerPivot & DAX Formulas for Excel 2010
This dialog has a More Options button that leads to a More Sort Options dialog. This
dialog is shown in Figure 5-27. It contains a really useful drop-down labeled First Key
Sort Order—this is invaluable when you want to sort alphabetic months of the year
or days of the week in their numeric order. You can sort months, for example, from
January to December, and override an alphabetic sort.
The second section of the Column Labels/Row Labels drop-down menu is for
filtering. The first of the three entries in this section is for clearing any existing filter.
The second and third entries are for Label Filters and Value Filters, respectively. Each
of these has its own fly-out menu. These fly-out menus, in turn, have options that lead
to dialogs (apart from the Clear Filter option) where sophisticated filtering can be
implemented. Label Filters are filters based on the values of the column. Value Filters
are filters based on the values of a measure in the data area of the pivot table—although
you can also base them on counts of values in columns. The Label Filters fly-out is
shown in Figure 5-28. All of the options (apart from Clear Filter) lead to the Label
Filter dialog as shown in Figure 5-29. This dialog includes 14 different comparison
operators or phrases. In the example shown, both Argentina and Austria will be
removed from the list of countries. To remove Label Filters, click Clear Filter in the
Column Labels/Row Labels drop-down menu.
Figure 5-27 More Sort Options dialog
Chapter 5: Pivot Tables and Pivot Charts: Overview 155
The Value Filters fly-out menu is shown in Figure 5-30. All of the options (apart
from Clear Filter) result in a dialog. This dialog is entitled Value Filter for eight of the
nine options that open a dialog. The Top 10 option opens the Top 10 Filter dialog. In
either of these two dialogs, you can filter the column or row labels by the value of a
measure in the data area of the pivot table. The Value Filter dialog is shown in Figure 5-31.
The dialog has a total of eight comparison operators or phrases. The Top 10 Filter
dialog is shown in Figure 5-32. This dialog is where you choose to view the top or
bottom performers in relation to a particular measure.
Figure 5-28 Label Filters fly-out menu
Figure 5-29 Label Filter dialog
156 Practical PowerPivot & DAX Formulas for Excel 2010
Label Filters and Value Filters are mutually exclusive. You can’t have both Label
Filters and Value Filters. Strictly speaking, that’s not quite true. You can have both
Label Filters and Value Filters, but not through the GUI. You have to write the MDX
(not DAX!) to create a named set. This alternative is available through the Fields, Items,
& Sets button in the Calculations group of the PivotTable Tools/Options ribbon. This
is an advanced topic and is introduced in Chapter 12.
The final section of the Column Labels/Row Labels drop-down menu is for
searching for entries and implementing filtering by selecting/deselecting check boxes.
There is also a context menu for entries in rows and columns. This, too, allows you
to sort and filter. The important options in this menu are covered elsewhere in this
chapter—there are often alternative ways of reaching the same goal in a pivot table.
Data Area Context Menu
When you add a column or measure to the Values drop-zone, it appears in the data area
of the pivot tables. Usually, you would want to add a numeric column or measure. If it’s
a non-numeric column, the only useful aggregation is that of Count. The net result is
Figure 5-30 Value Filters fly-out menu
Figure 5-31 Value Filter dialog
Chapter 5: Pivot Tables and Pivot Charts: Overview 157
one or more figures or values in the data area. If you right-click on a cell containing a
value, a context menu appears. This is shown in Figure 5-33.
This menu includes many options. Some of these lead to dialogs, some to fly-out
menus. Some of the options are very powerful indeed—Show Values As is particularly
powerful and can transform your pivot tables from business intelligence into business
wisdom! Show Values As, therefore, is covered in great detail shortly, in the practical
later in this section. Not all of the options in the context menu are covered here. Some
are available through a ribbon as well and will be mentioned when we reach the section
of this chapter on ribbons. Some are fairly obvious, for example, Copy or Remove, and
will not be discussed. The Additional Actions option is only relevant for pivot tables
based directly on SSAS cubes and not for those based on PowerPivot models, at least in
this release of PowerPivot. The ones remaining, and covered here, include Format Cells,
Number Format, Sort, and Show Values As.
Figure 5-32 Top 10 Filter dialog
Figure 5-33 Data area context menu
158 Practical PowerPivot & DAX Formulas for Excel 2010
The Format Cells option opens the Format Cells dialog. This dialog is shown in
Figure 5-34 with the Font tab selected. One thing you might consider doing here is
choosing a color for the data values. Format Cells only works on the current cell. If
you want to format all the cells containing the value, use Number Format instead.
Alternatively, you can go through the Field Settings button in the Active Field group
of the PivotTable Tools/Options ribbon.
Unlike Format Cells, Number Format affects all of the cells that contain the measure.
Number Format also leads to the Format Cells dialog, but only the Number tab is
available. This tab of the dialog is shown in Figure 5-35. This option is valuable when
you want to quickly apply a numeric format, such as Currency or Percentage, to all the
values of a particular measure or column in the data area.
The Sort option has a fly-out menu. This is useful for quickly sorting from smallest
to largest (or vice versa) on a measure. If you have the same measure in multiple
columns (because you have a column in Column Labels), the sort is applied to the rows
based on values in the current column. The Sort fly-out menu is shown in Figure 5-36.
Figure 5-34 Format Cells dialog—Font tab
Chapter 5: Pivot Tables and Pivot Charts: Overview 159
The Show Values As option also has a fly-out menu. This menu, in itself, has many
options. These are among the most useful and powerful options in a pivot table, and
they can greatly enhance your business intelligence. Consequently, you may want to
follow along with the following practical. The exercises assume you have the Northwind
PowerPivot model. Chapter 1 showed how to build this model on a SQL Server
Northwind source. Chapter 2 showed how to import Northwind from Access, from Excel
(you can download the one for Excel from this book’s Web site), or from a data feed.
Figure 5-35 Format Cells dialog—Number tab
Figure 5-36 Sort fly-out menu
160 Practical PowerPivot & DAX Formulas for Excel 2010
You’ll also need a couple of calculated columns that have been used in a few chapters in
this book. These are the Year column in the Orders table and the Sales Amount column
in the Order Details table. As a reminder, their respective DAX formulas are:
=YEAR(Orders[OrderDate])
='Order Details'[Quantity] * 'Order Details'[UnitPrice]
There are various incarnations of Northwind, so your results may differ from mine.
That’s not too important; what is important is to see how to implement some real BI.
So, here are the step-by-step instructions:
1. Add Year (from Orders) to Column Labels and add Country (from Customers)
to Row Labels.
2. Add Sales Amount (from Order Details) to Values. Right-click its entry in Values
and choose Edit Measure. In the Measure Settings dialog, change the measure’s
name from Sum of Sales Amount to Sales Amount.
3. Drag Sales Amount, again, from the Order Details table to Values and position it
below the first entry. Right-click and choose Edit Measure. Call this second entry
Calculation. Your PowerPivot Field List should look like Figure 5-37, and your
pivot table like Figure 5-38.
Figure 5-37 PowerPivot Field List
Chapter 5: Pivot Tables and Pivot Charts: Overview 161
4. Right-click on any value in the Calculation column in the pivot table. This opens
the data area context menu. From the menu, choose Show Values As to show its
fly-out menu. This menu is shown in Figure 5-39.
5. From the fly-out, choose % of Grand Total. The result is shown in Figure 5-40;
the screenshot does not show all of the results. According to my version of
Northwind, Germany was a pretty good market in 1997.
6. Repeat the previous two steps, but this time, choose % of Parent Row Total.
Figure 5-41 shows the result. The USA was the top market in 1996, being
responsible for nearly a fifth of all sales. Please note that the Grand Total row at
the bottom is showing 100%.
7. This time, try % Difference From. Unlike the previous two examples, this one
has an ellipsis and opens a dialog, called Show Values As. The dialog is shown in
Figure 5-42.
Figure 5-38 Pivot table
162 Practical PowerPivot & DAX Formulas for Excel 2010
Figure 5-39 Show Values As fly-out menu
Figure 5-40 % of Grand Total
Chapter 5: Pivot Tables and Pivot Charts: Overview 163
8. In this dialog, select Year as the Base Field, and (previous) as the Base Item, before
clicking OK. My result is shown in Figure 5-43. The result of the calculation for
1996 is blank as there were no sales in the previous year, 1995. Sales in Denmark
showed a dramatic increase from 1996 to 1997.
Figure 5-41 % of Parent Row Total
Figure 5-42 Show Values As dialog