Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
134 Practical PowerPivot & DAX Formulas for Excel 2010
encapsulated and not appear in the CALCULATE() function. When CALCULATE()
is based directly on another measure, you can omit CALCULATE() itself, but the
filter parameters must be enclosed inside parentheses. The measure itself acts as a
function. If you want to try, create a measure called QuantitySum and amend the last
CALCULATE() example in Step 12. Here are the two DAX formulas you will need:
=SUM('Order Details'[Quantity])
=CALCULATE([QuantitySum],
Customers[Country]="Austria" || Customers[Country]="Mexico",
Orders[Year]=1996)
Now change the CALCULATE() formula, as shown next, to use the shorthand:
=[QuantitySum](Customers[Country]="Austria" ||
Customers[Country]="Mexico",Orders[Year]=1996)
Please note that the filter parameters are enclosed in parentheses and there is no
comma (,) after the measure name.
This section has been a fairly detailed introduction to the CALCULATE() function.
To reiterate, CALCULATE() can be used to control the filter context of measures within
a PowerPivot pivot table.
Where to Go from Here
This chapter has been an overview of and introduction to DAX. As such, only a very
few functions have been covered. In particular, the very useful date and time functions
have not been mentioned. These functions enable you to build time intelligence—for
example, year-on-year changes and year-to-date figures. The second part of this book
(Part II, DAX) delves much more deeply into DAX. It looks at every DAX function.
Among many other techniques, it demonstrates how to include time intelligence in
your PowerPivot pivot tables.
Pivot Tables and Pivot
Charts: Overview
Chapter 5
136 Practical PowerPivot & DAX Formulas for Excel 2010
T
his chapter is concerned with looking at pivot reports, that is, pivot tables and
pivot charts. The chapter is an overview; a whole book could easily be written
about pivot tables in Excel—and another one just on pivot charts! The emphasis
here is on creating pivot tables and charts. The most important focus is on how to add
data to a pivot report using the PowerPivot Field List. But, once this is done, there are
dozens of context menus, drop-down menus, and quite a few ribbons with dozens of
buttons. Some of these are explored in this chapter, especially where they are particularly
relevant to working with PowerPivot. These menu options and buttons help you
organize, control, and format your pivot reports. By the end of the chapter, you should
be in a position to create meaningful and attractive pivot tables and charts.
C
Key concepts Creating pivot tables, creating pivot charts, linking pivot charts
to pivot tables, using the PowerPivot Field List with pivot tables, using the
PowerPivot Field List with pivot charts, organizing pivot reports, controlling pivot
reports, formatting pivot reports
Background to Pivot Reports
A pivot report is either a pivot table or a pivot chart. A pivot table shows data as values
or figures. A pivot chart is a graphical representation of data. Both, of course, have their
place in business intelligence. You might use a pivot chart to monitor your business,
with its easy high-level visualization of business metrics. You might use a pivot table
to analyze those metrics in a little more depth and detail. Often, you might want both,
perhaps with the chart reflecting the data in the table. Alternatively, your chart may
well show different data from your table. In addition, you can have multiple pivot tables
and charts. Excel and PowerPivot provide many options for inserting pivot reports. The
number of options available can be a little overwhelming at first. There are so many
options, as there are different types of pivot reports supported by Excel.
A pivot report can be based directly on worksheet data—you could use the
PivotTable button on the Insert ribbon to do this. Or, you might want a pivot report
based directly on an SSAS cube. You can accomplish this through the From Other
Sources button on the Data ribbon. Yet again, you can have a pivot report based on a
PowerPivot model. In that case you might insert the report through the PivotTable
button on the PowerPivot ribbon, or through the PivotTable button on the Home
ribbon in the PowerPivot window.
There are also a number of options for inserting a pivot table and a related or an
unrelated pivot chart. In addition, there are different ways of inserting supporting
objects, such as slicers. To help cut through the complexity, this chapter is devoted only
Chapter 5: Pivot Tables and Pivot Charts: Overview 137
to pivot reports based on PowerPivot models. Furthermore, pivot tables and pivot charts
will be considered separately—the longer section on pivot tables coming first. The later
section on pivot charts will demonstrate how to link a chart to a table and how to have
an independent chart. And, where there are alternative methods of reaching the same
goal, the easiest and most direct alternative will be shown.
Pivot Tables
When you want to create a pivot report, the choice is between a pivot table and a pivot
chart, or both together. You can, of course, add further tables and charts retrospectively,
whether related or unrelated to the first reports inserted. Here, we discuss how to insert
a single pivot table.
Creating a Pivot Table
As the pivot tables shown in this book are based on a PowerPivot model, you must first
have some data in the PowerPivot model. Once the data is assembled, you are ready
to view it in a pivot table. If you change the PowerPivot model later, you can easily
update the field list by clicking the Refresh button that will appear at the top of the
PowerPivot Field List for the pivot table.
There are two main ways of inserting a pivot table (as stated earlier, this chapter
concentrates on the most direct and easiest alternatives and, for the sake of clarity,
does not mention every single possibility). One way to create a pivot table is to click
the PivotTable button in the Reports group on the Home ribbon in PowerPivot. The
second way is to click the PivotTable button in the Report group on the PowerPivot
ribbon in Excel. Both of these result in the same drop-down menu. This menu is shown
in Figure 5-1.
In this menu, there are two choices for inserting a stand-alone pivot table (that is,
a pivot table without a related pivot chart). One option is called PivotTable and the
other Flattened PivotTable. There have been many examples of the former throughout
this book—but, to show the comparison between a “normal” and a flattened pivot table,
Figure 5-2 shows a typical result after choosing PivotTable. Figure 5-3 shows a typical
result after choosing Flattened PivotTable.
A flattened pivot table looks more like an Excel table. It’s flattened, or completely
expanded, and there is no option to collapse column or row labels. A simpler display
sacrifices the ability to drill up or drill down.
Before the pivot table is inserted, the Create PivotTable dialog allows you to decide
upon the location of the pivot table. This dialog is shown in Figure 5-4.
138 Practical PowerPivot & DAX Formulas for Excel 2010
Figure 5-1 Inserting a pivot table menu
Figure 5-2 Unflattened pivot table
Chapter 5: Pivot Tables and Pivot Charts: Overview 139
Figure 5-3 Flattened pivot table
Figure 5-4 Create PivotTable dialog
140 Practical PowerPivot & DAX Formulas for Excel 2010
PowerPivot Field List for Pivot Tables
Once the pivot table has been inserted into an Excel worksheet, you control the
data that appears through the PowerPivot Field List. This field list has a slightly
different appearance when you are working with a pivot chart. In this section, we are
concentrating on the PowerPivot Field List that accompanies a pivot table. An example
of a PowerPivot Field List is shown in Figure 5-5.
Often, the PowerPivot Field List will simply disappear. It’s sensitive to context, so it
only shows when the focus is within a pivot table. If you have a Report Filter, you can
also place the focus there to display a hidden field list. Placing the focus on a slicer does
not cause a hidden field list to appear. If you still can’t see the PowerPivot Field List,
make sure the Field List toggle button, in the Show/Hide group on the PowerPivot
ribbon, is turned on.
Let’s spend a little time looking at the PowerPivot Field List. By default, it’s docked
on the right-hand side of the worksheet containing the pivot table. You can undock it
simply by dragging on its title bar so that it becomes free-floating. You can then dock it
on any side of the worksheet by dragging far enough. Alternatively, if it’s free-floating,
you can redock it in the last docked position by double-clicking its title bar. If you click
Figure 5-5 PowerPivot Field List for a pivot table
Chapter 5: Pivot Tables and Pivot Charts: Overview 141
the Close button at the right of the title bar, the PowerPivot Field List gets hidden,
even if the focus is in the pivot table. To redisplay it, click the Field List button on the
PowerPivot ribbon. Immediately to the left of the Close button is a small button that
opens a drop-down menu. This menu gives you choices for moving, sizing, or closing
the field list. The menu is shown in Figure 5-6. Of course, it’s possible to resize the field
list by dragging its left-hand border (assuming it’s docked on the right-hand side of the
worksheet).
Just below the title bar is a text box for searching the field list. However, there
may well be another entry between the title bar and the search text box. This area is
sometimes referred to as the message area. There are two possible messages that are
displayed in this message area. One informs you that relationships are needed between
tables. This can happen if suitable table relationships have not been established in the
PowerPivot window. The message can only appear if the Detection toggle button, in
the Relationship group of the PowerPivot ribbon, is turned on (this is the default state).
If the message (“Relationship may be needed”) does appear, clicking the Create button
causes PowerPivot to attempt to find and create the required relationship. A lack of
suitable relationships between tables in the PowerPivot model may result in inaccurate
and misleading data appearing in the pivot table. This relationship message is shown in
Figure 5-7.
The second message that may show occurs when the PowerPivot model is changed
after the pivot table has been created. For example, you may have returned to the
PowerPivot window and created a new calculated column in a table, or added a new
table to the model. To the right of the message (“PowerPivot data was modified”) is a
Refresh button. Clicking this button updates the PowerPivot Field List to reflect the
changes made in the PowerPivot model. This message is shown in Figure 5-8.
If there is a message in the message area, the search text box is underneath the
message. If there’s no message, the search box is immediately underneath the title
bar. This text box is used to search for tables, regular columns, calculated columns, or
calculated measures in the table and column list area of the PowerPivot Field List. This
is handy when there are many tables and columns and measures. If you search for a
column or a measure, and its containing table is collapsed, the search will automatically
expand the table to reveal its columns and measures. If there are multiple potential
results, the arrow buttons step through the entries. Figure 5-9 shows the result of
searching for the ShipCountry column.
Figure 5-6 PowerPivot Field List drop-down menu
142 Practical PowerPivot & DAX Formulas for Excel 2010
Figure 5-7 “Relationship needed” message
Figure 5-8 “Data modified” message
Chapter 5: Pivot Tables and Pivot Charts: Overview 143
Apart from a few of the features already discussed, the PowerPivot Field List does
not contain ribbons or buttons or left-click menus. However, there are a number of
context-sensitive, right-click menus. As they are context-sensitive, these pop-up menus
may well present different choices. The next few sections take a look at these context
menus.
Table Context Menu
To see the table context menu, right-click on any table that appears in the table and
column list section of the PowerPivot Field List. The menu is shown in Figure 5-10.
The first four choices in this menu are for expanding or collapsing either the selected
table or all tables. The fifth entry, Add New Measure, is very useful. If you choose this
option, the new calculated measure will automatically default to the selected table.
It means that you don’t have to change the Table name property in the subsequent
Measure Settings dialog. The Measure Settings dialog is shown in Figure 5-11.
Figure 5-9 Searching the PowerPivot Field List