Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
164 Practical PowerPivot & DAX Formulas for Excel 2010
9.
Often, you may want to see running totals. This next example may prove quite
useful. From the fly-out menu, choose Running Total In. You can right-click
on any value for Calculation, including blanks. Once again, you’ll see the Show
Values As dialog, but, this time, there is only one setting. Make Year the Base
Field, before clicking OK. The result is shown in Figure 5-44. The running total
for Argentina in 1996 is zero, as its sales were blank that year. In general, if it’s a
numeric calculation, blanks are treated as zeros.
10. Let’s have a look at one final example. This time, choose Rank Largest to
Smallest. In the Show Values As dialog, confirm that Country is the Base Field,
and click OK. The result is shown in Figure 5-45. Is our sales team doing well in
Sweden? It’s climbed from number 10 to 8 to 5, in just two years!
Hopefully, these few examples demonstrate a little of the tremendous BI potential
of pivot tables. There are, perhaps, two observations to make. One, you are not limited
Figure 5-43 % Difference From
Chapter 5: Pivot Tables and Pivot Charts: Overview 165
to just the one calculation at a time. If you want more than one, simply add the relevant
column or measure more times to the Values drop-zone, and perform a different
calculation on each one. Two, if a pivot table has so many possibilities, why do we need
DAX? If these examples are BI, calculations based on DAX formulas are BI on steroids.
For example, if your sales amounts were in different currencies, you could apply a
conversion factor, based on the country name, to convert sales into a common currency
before ranking.
PivotTable Tools/Options Ribbon
The previous section demonstrated how to use a pivot table. This section and the next
few sections discuss a few of the options available for customizing the appearance of
a pivot table. It involves a quick look at a couple of Excel ribbons. We start with the
PivotTable Tools/Options ribbon, which is shown in Figure 5-46. Not every single
Figure 5-44 Running Total In
166 Practical PowerPivot & DAX Formulas for Excel 2010
item on this ribbon (and others) will be explored. Some ribbon buttons and menus have
alternatives discussed elsewhere, or are simply not relevant—for example, OLAP Tools |
Offline OLAP is not going to work. And some are even going to cause confusion—for
example, the Field List button creates a second (non-PowerPivot) field list, and Insert
Slicer creates a slicer that does not appear in the PowerPivot Field List drop-zones.
Figure 5-45 Rank Largest to Smallest
Figure 5-46 PivotTable Tools/Options ribbon
Chapter 5: Pivot Tables and Pivot Charts: Overview 167
PivotTable Group This is the first group on the ribbon. If you click Options
followed by Options, once again, you open the PivotTable Options dialog. This has
a number of tabs, a couple of which will be examined here. The first tab is Layout &
Format, and is shown in Figure 5-47.
Two useful options on this tab are in the Format section. They are labeled “For Error
Values Show” and “For Empty Cells Show.” You may, for instance, wish empty or blank
cells to display a zero, or perhaps a string such as “NA.”
The next tab, shown in Figure 5-48, is the Totals & Filters tab.
This tab includes a Grand Totals section that allows you to toggle grand totals for
both rows and columns. This functionality is duplicated on the PivotTable Tools/
Design ribbon, where it is, perhaps, more accessible.
The third tab is the Display tab. This tab is shown in Figure 5-49.
A handy (or dangerous!) option on this tab is to suppress the Column Labels and
Row Labels drop-down menus. This option is perhaps easier to access through the
Field Headers button in the Show group of the ribbon.
Figure 5-47 PivotTable Options dialog—Layout & Format tab
168 Practical PowerPivot & DAX Formulas for Excel 2010
Actions Group The Clear button has a Clear All option in its drop-down menu.
Clear All removes all the entries from the PowerPivot Field List and is a quick way to
start redesigning your pivot table. It does not delete the pivot table from the worksheet.
The Select button has an Entire PivotTable option in its drop-down. If you follow
this with the correct sequence of clicks, you can not only clear the pivot table, but also
remove the pivot table altogether. The sequence required is Home ribbon | Cells group
Delete button | Delete Cells.
Calculations Group The Fields, Items, & Sets button opens a drop-down menu.
From this menu you can open the New Set dialog. In this dialog you can create new
sets graphically or by writing MDX. A set is a customizable list of values from a column.
You can exploit this to produce preconfigured and prefiltered entries for your pivot
table. This is an advanced topic, and is mentioned in Chapter 12. To implement this, it
Figure 5-48 PivotTable Options dialog—Totals & Filters tab
Chapter 5: Pivot Tables and Pivot Charts: Overview 169
helps if you have some experience with SSAS cubes and MDX (not DAX). When you
create a set, it appears as a column under a parent node called Sets in the PowerPivot
Field List, and from there it can be added to a pivot table or pivot chart.
Tools Group This group includes the all-important PivotChart button. Use this to
insert a pivot chart that is linked to the current pivot table.
Show Group Please try not to click the Field List button in this group! If you do, you
will end up with two field lists for your pivot table. The +/– Buttons button is a toggle
that disables or re-enables the + and – buttons that you see when you drill down on
Column Labels or Row Labels. If you disable these buttons, you can still expand and
collapse by right-clicking. The Field Headers button hides or shows the Column Labels
and Row Labels captions and drop-downs.
Figure 5-49 PivotTable Options dialog—Display tab
170 Practical PowerPivot & DAX Formulas for Excel 2010
PivotTable Tools/Design Ribbon
This is the second of the two PivotTable Tools ribbons. It is shown in Figure 5-50.
As its name suggests, the Design ribbon is where you set the appearance of a pivot
table. The appearance is quite important, not only from a cosmetic perspective, but also
to hide or show data. If you would like to experiment, place Year in Column Labels,
Country then City in Row Labels, and Sales Amount in Values. Use the Row Labels
drop-down to filter the country and show Argentina and Austria only. Your pivot table
should look something like Figure 5-51. The screenshot is using all the default settings,
before any design changes.
Layout Group The Subtotals button opens a drop-down menu. This menu is shown
in Figure 5-52.
From this menu, try turning off subtotals and then showing them at the bottom. The
final result is shown in Figure 5-53. When you are finished, set subtotals back to display
at the top.
Figure 5-50 PivotTable Tools/Design ribbon
Figure 5-51 Pivot table filtered on Argentina and Austria
Chapter 5: Pivot Tables and Pivot Charts: Overview 171
If you observe the grand totals at the bottom, they are equal to the total of the
subtotals for Argentina and Austria. Now, from the same drop-down, choose to
include filtered items in totals. The result is shown in Figure 5-54. The grand totals
now include the subtotals for those countries that are filtered out, as well as those for
Argentina and Austria. If you are from an SSAS background, you have just disabled
visual totals. When you are finished, reverse the process, and exclude filtered items.
The Grand Totals button also has a drop-down menu. This is shown in Figure 5-55.
If you turn off grand totals for rows and columns, your pivot table looks like Figure 5-56.
When you are done, re-enable the grand totals for rows and columns.
Figure 5-52 Subtotals drop-down menu
Figure 5-53 Subtotals at the bottom
172 Practical PowerPivot & DAX Formulas for Excel 2010
Figure 5-54 Filtered items included in totals
Figure 5-55 Grand Totals drop-down menu
Figure 5-56 Grand Totals turned off for rows and columns
Chapter 5: Pivot Tables and Pivot Charts: Overview 173
The Report Layout button drop-down is shown in Figure 5-57. From this drop-down,
try tabular layout followed by outline layout. The final result is shown in Figure 5-58. This
format creates extra columns for labels. After you finish experimenting, revert back to the
default compact layout.
The Blank Rows button drop-down is shown in Figure 5-59. Try inserting a blank
line after each item—the result is shown in Figure 5-60. You may want to reset your
pivot table by turning off blank lines when you are finished.
PivotTable Style Options In this group, you might like to try out the four check
boxes. Notice how Banded Rows and Banded Columns amend the overall style of the
pivot table in the PivotTable Styles group.
PivotTable Styles This group has a single button with a drop-down. If you open
the drop-down, and hover the mouse over a style, you have a live preview of the effect
of the style on your pivot table. At the bottom of the drop-down, you have options to
remove your current style or even to create your own styles.
Page Layout and View Ribbons
The Page Layout ribbon has a Sheet Options group and the View ribbon has a Show
group. Check boxes in these groups can be used to suppress gridlines and headings in
the worksheet. Only the View/Show group has an option to hide the Excel formula
bar. You may want to hide items before sharing your worksheet and pivot table. A
perfect way to share your PowerPivot pivot table is to save or publish to PowerPivot for
SharePoint. You can achieve this by clicking File | Save & Send | Save to SharePoint.
Figure 5-57 Report Layout drop-down menu