Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
144 Practical PowerPivot & DAX Formulas for Excel 2010
Column Context Menu
You can have both regular and calculated columns in the PowerPivot Field List. As a
reminder, a regular column is part of the data imported into the PowerPivot model—a
calculated column is added later using a DAX formula. Both types of columns look the
same in the PowerPivot Field List, and both have the same context menu. If it’s important
to distinguish between regular and calculated columns, you can adopt a naming convention.
The context menu for a column is shown in Figure 5-12. The second, third, and fourth
choices in the menu are disabled; they are relevant to calculated measures only. The final
six entries in the menu allow you to add the column to one of the six drop-zones at the
foot of the PowerPivot Field list. You can, of course, accomplish that simply by dragging to
the required drop-zone. Adding a column to the Values drop-zone implicitly allows it to
function as a measure. By default, a numeric column will be summed, and a non-numeric
column counted (unless it can be implicitly converted into a number, like OrderID in our
Northwind model). Adding a column to any other drop-zone leaves it as a column (or a
non-measure dimension attribute, if you know your SSAS).
Figure 5-10 Table context menu
Figure 5-11 Measure Settings dialog
Chapter 5: Pivot Tables and Pivot Charts: Overview 145
Calculated Measure Context Menu
An explicit measure, or calculated measure, is distinguished from regular and calculated
columns by the small calculator icon after its name. Unlike for a column, the second,
third, and fourth options in the context menu are enabled for an explicit measure.
Clicking Edit Formula opens the Measure Settings dialog, so you can amend the DAX
formula for the measure. The only drop-zone available for a measure is Values. You can’t
place a measure in any of the other five drop-zones. The context menu for a measure is
shown in Figure 5-13.
Drop-Zone Entry Context Menu (Apart from Values)
There are six drop-zones in the PowerPivot Field List, corresponding to the areas in
or around a pivot table. The drop-zones are Slicers Vertical, Slicers Horizontal, Report
Filter, Column Labels, Row Labels, and Values. The Values drop-zone (corresponding
to the data area in a pivot table) is subtly different from the other five. As such, it’s
covered separately in the next section. Measures can only be added to the Values
Figure 5-12 Column context menu
Figure 5-13 Measure context menu
146 Practical PowerPivot & DAX Formulas for Excel 2010
drop-zone and not to any of the other five. None of the six drop-zones has its own
context menu, but context menus are enabled for any entry in the drop-zone. For
non-Values drop-zones, the entries can only be regular or calculated columns. For the
Values drop-zone, the entry may be a column or a measure.
Figure 5-14 shows the context menu when you right-click on a column in any
drop-zone (apart from Values). The first group of four choices allows you to change
the position of the entry within the current drop-zone (assuming you have more than
one entry). You can achieve the same thing by simply dragging the entry up or down.
The second group of six entries lets you move the column to another drop-zone. Again,
you can also do this by simply dragging from one drop-zone to another. The eleventh
option, Summarize By, is disabled—it only applies to columns in the Values drop-zone.
The Field Settings option opens the Field Settings dialog, where you can change the
Custom Name property for the column. The Field Settings dialog is shown in
Figure 5-15.
Figure 5-14 Drop-zone entry context menu (apart from Values)
Figure 5-15 Field Settings dialog
Chapter 5: Pivot Tables and Pivot Charts: Overview 147
Drop-Zone Entry Context Menu (Values Only)
An entry in the Values drop-zone can be either a measure or a column (regular
or calculated). The context menu (shown in Figure 5-16) for each type of entry is
slightly different. If it’s a measure, the menu does not allow you to move the entry to
another drop-zone. Also, if it’s a measure, the Summarize By option is disabled—your
aggregation will be part of the DAX formula for the measure. Right-clicking on a
measure and choosing Edit Measure opens the Measure Settings dialog for that measure,
as shown in Figure 5-17. Right-clicking on a column and choosing Edit Measure
opens the Measure Settings dialog for that column—this is shown in Figure 5-18.
Figure 5-16 Drop-zone entry context menu (Values only)
Figure 5-17 Measure Settings dialog for a measure
148 Practical PowerPivot & DAX Formulas for Excel 2010
The Measure Settings dialog for a column lets you change the aggregation function for
that column—clicking Summarize By does the same. The Measure Settings dialog for
a column lets you change the name for that column. For example, if you add the Sales
Amount column from the Order Details table to the Values drop-zone, it’s called Sum of
Sales Amount, and you may want to change this back to Sales Amount. The Summarize
By option in the context menu is only enabled for columns. This leads to a fly-out menu
(shown in Figure 5-19), which presents another way to change the aggregation on a
column. The aggregation choices are Sum, Count, Min, Max, and Average. The only
one that makes sense for a non-numeric column is Count. All of these aggregations are
also available for measures—in that case, the aggregation would be defined in the DAX
formula for the measure. Using a DAX formula for a measure provides much more
flexibility than using Summarize By for a numeric column.
Figure 5-18 Measure Settings dialog for a column
Figure 5-19 Summarize By fly-out menu for a column
Chapter 5: Pivot Tables and Pivot Charts: Overview 149
Slicers for Pivot Tables
Adding slicers to a pivot table is best done in the PowerPivot Field list by placing
columns in either Slicers Vertical or Slicers Horizontal. You can also add slicers from
the Insert ribbon, but the slicer does not get added to the PowerPivot Field List. Yet
another alternative is to add a slicer from the PivotTable Tools/Options ribbon.
Slicers too have their own context menu. This menu (shown in Figure 5-20) has a
large number of options—only the most interesting from a PowerPivot perspective are
discussed here. There are three options for sorting the buttons in a slicer and one option
for removing a filter. You can also control the sort order of the buttons by choosing
Slicer Settings. A filter can be removed more simply by clicking the filter button at the
top right of the slicer.
Choosing Size and Properties opens the Size and Properties dialog. The Position
and Layout page has a useful setting, called Number of Columns. This page of the
dialog is shown in Figure 5-21. Selecting Slicer Settings in the context menu opens
the Slicer Settings dialog. This dialog is shown in Figure 5-22. In the Slicer Settings
dialog you can control how the items in the slicer are sorted. In addition, you can decide
whether to have a header or not, and its caption. There are further options that apply to
items with no data—by default, these appear as buttons without a caption in the slicer.
You may want these to be hidden in your slicer.
Figure 5-20 Slicer context menu
150 Practical PowerPivot & DAX Formulas for Excel 2010
Figure 5-21 Position and Layout page of the Size and Properties dialog
Figure 5-22 Slicer Settings dialog
Chapter 5: Pivot Tables and Pivot Charts: Overview 151
Pivot Table Menus and Ribbons
The pivot table itself has context menus too. Lots of them! Whole books have been
written, and will be written, about using and customizing pivot tables. In a single chapter,
not every single avenue can be followed. Instead, the emphasis is on those options that
are most useful and interesting from a PowerPivot perspective. For the purposes of this
section, a pivot table is defined as the areas corresponding to the Report Filter, Column
Labels, Row Labels, and Values drop-zones of the PowerPivot Field List. As pivot table
areas, they all have context menus and/or drop-down menus. In addition, there are
ribbons that both duplicate and extend some of the options available through the menus.
To avoid undue repetition, not every alternative is explored—the emphasis here is on ease
of implementation. The following sections detail some of the drop-down menu options,
context menu options, and ribbon button functionality for PowerPivot pivot tables.
Report Filter Drop-Down Menu
When you add a column to the Report Filter drop-zone in the PowerPivot Field List,
a filter will appear immediately above the main body of the pivot table. You can add
more than one Report Filter, if you wish. Each filter consists of two cells. The first cell
shows the column name, while the second cell shows the current filter on that column.
By default, the filter is All. The second cell contains a small button that leads to a drop-
down menu. The sole purpose of this menu is to enable you to set a filter context for
the data in the pivot table. In many ways, a Report Filter is similar to a slicer. The drop-
down menu for a Report Filter can have one of two possible formats. The appearance is
governed by the Select Multiple Items check box at the bottom of the menu. The two
possible formats are shown in Figures 5-23 and 5-24.
Figure 5-23 Report Filter drop-down menu without multiselect
152 Practical PowerPivot & DAX Formulas for Excel 2010
The example shown is for the LastName column from the Employees table in
Northwind. There are only a few entries to browse before making a decision about a
filter. When you have lots of entries, the search box at the top of the menu is very useful.
Column Labels/Row Labels Drop-Down Menu
When you add a column to Column Labels, a cell with the caption Column Labels
appears in the pivot table. This cell has a small button that opens the Column Labels
drop-down menu. The same is true when you add a column to Row Labels. Both of the
drop-downs are the same—an example (using the Country column from the Customers
table) is shown in Figure 5-25. Please note that it is possible to hide the cell, and
then the drop-down menu is unavailable. Its visibility is determined by the setting for
Display Field Captions and Filter Drop Downs on the Display tab of the PivotTable
Options dialog. This dialog is accessed through the Options button in the PivotTable
group of the PivotTable Tools/Options ribbon.
This drop-down menu is more feature-rich than the one for a Report Filter. It allows
you to sort, not just filter—and the filter options are more sophisticated. The menu
is divided into three sections. The first section contains three options and is devoted
to sorting. The second section, to do with filtering, also has three options. The third
section includes a search box and all the labels (or captions) for the column. In the first
section, you can sort in an ascending or descending order. If you require more control
over sorting, click More Sort Options. This opens the Sort dialog shown in Figure 5-26.
In the example demonstrated here (on the Country column from the Customers table),
the sort has been defined as descending on the value of the Sales Amount in the data area.
Figure 5-24 Report Filter drop-down menu with multiselect
Chapter 5: Pivot Tables and Pivot Charts: Overview 153
Figure 5-25 Column Labels/Row Labels drop-down menu
Figure 5-26 Sort dialog