Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
174 Practical PowerPivot & DAX Formulas for Excel 2010
Home Ribbon
On the Home ribbon there is a Format As Table button in the Styles group. You can
use this to alter the overall appearance of the pivot table. If you do so, it may change the
pivot table styles in the PivotTable Tools/Design ribbon.
Also in the Styles group is the Conditional Formatting button. This is a very
powerful and useful feature, so you may want to follow these quick step-by-step
instructions:
1. Start by preparing a pivot table that resembles the one shown in Figure 5-61. Or,
you could easily adapt the steps to your own pivot table. The example shown here
has been filtered to show only Argentina and Austria as countries. In addition, the
Column Labels and Row Labels have been hidden—through the Field Headers
button on the PivotTable Tools/Options ribbon.
2. Click on a subtotal value for any year, for any country. You can even use the blank
entry for Argentina in 1996.
Figure 5-58 Pivot table in outline form
Figure 5-59 Blank Rows drop-down menu
Chapter 5: Pivot Tables and Pivot Charts: Overview 175
3. Click the Conditional Formatting button in the Styles group of the Home ribbon.
This opens a drop-down menu, which is shown in Figure 5-62.
4. From this drop-down, hover the mouse over Data Bars to see its fly-out menu, as
shown in Figure 5-63.
5. In this fly-out menu click More Rules, at the bottom of the menu, to open the
New Formatting Rule dialog. The dialog is shown in Figure 5-64.
6. Toward the top of this dialog are three option buttons. Turn on the third of
these—this means the conditional formatting will be applied to every subtotal
for every country and every year. Click OK and you should see colored data bars
in the pivot table. In my example, the one for Argentina in 1997 is quite narrow,
while the one for Austria in the same year is much wider. You have quick visual
cues to the size of the values.
Figure 5-60 Pivot table with blank lines
Figure 5-61 Pivot table without conditional formatting
176 Practical PowerPivot & DAX Formulas for Excel 2010
7.
Select any cell for the subtotal for a city. Repeat steps 3 through 6, but choose a
different color in the New Formatting Rule dialog—the color for the fill in the
Bar Appearance section of the dialog. Remember to turn on the third option
button, before clicking OK. The final effect can be seen in Figure 5-65, although
the two colors don’t display well in monochrome printing!
Figure 5-62 Conditional Formatting drop-down menu
Figure 5-63 Data Bars fly-out menu
Chapter 5: Pivot Tables and Pivot Charts: Overview 177
Figure 5-64 New Formatting Rule dialog
Figure 5-65 Pivot table with conditional formatting
178 Practical PowerPivot & DAX Formulas for Excel 2010
Pivot Charts
You are probably not surprised to learn that there are many different ways of creating a
pivot chart. How you do so is dictated by your requirements. Do you want a single chart
or multiple charts? Should a chart be linked to an existing pivot table or created from
scratch? Would you like to create a pivot chart and a pivot table at the same time? If
you create them at the same time, how do you link or separate them?
Creating a Pivot Chart
A pivot chart always works in conjunction with a pivot table. If you create a pivot
chart, and it’s not linked to a pivot table, a separate pivot table is created on a separate
worksheet to underlie the pivot chart. You can create a pivot chart, against your current
PowerPivot model, from the PivotTable button on the Excel PowerPivot ribbon, or on
the PowerPivot Home ribbon. When you do so, you can create one, two, or four charts.
If you opt for a single pivot chart, you can decide whether you want a pivot table at the
same time, or not. If you create both a pivot chart and a table, they are independent—
changes to the data in one do not affect the other one. Instead, another pivot table,
linked to the pivot chart, is automatically added to another worksheet. You end up
with one chart and two tables, the pivot table on the same worksheet as the pivot chart
being independent. When you insert more than one chart, each chart has its own
PowerPivot Field List and its own linked pivot table. Each of the latter also has its
own PowerPivot Field List.
Another approach is to start just with a pivot table, using the PivotTable button in
Excel or the PowerPivot window, and adding the pivot chart later. Should you adopt
this approach, then the pivot chart you create can be independent of the pivot table or
linked. To insert a pivot chart that is not linked, use the PivotTable button in Excel or
PowerPivot again. To insert a pivot chart that is linked to your table, use the PivotChart
button in the Tools group on the PivotTable Tools/Options ribbon in Excel.
Linking a pivot chart to an existing pivot table is quite useful for BI. It enables you
to analyze the same scenario graphically and with numbers at the same time. The pivot
chart will have its own PowerPivot Field List, but it will be interdependent with the pivot
table PowerPivot Field List—if you make changes in one field list, it will update the other
one. The next set of step-by-step instructions shows a linked pivot table and chart.
PowerPivot Field List for Pivot Charts
The PowerPivot Field List for a pivot chart is shown in Figure 5-66.
This field list is pretty similar to the field list you see for a pivot table, apart from two
of the six drop-zones—Column Labels and Row Labels. Column Labels has a new title,
Legend Fields (Series). The Row Labels drop-zone has become Axis Fields (Categories).
Chapter 5: Pivot Tables and Pivot Charts: Overview 179
The Values drop-zone corresponds to the y-axis or value axis of a chart. The Axis Fields
drop-zone corresponds to the x-axis or categories axis of a chart, while the Legend
Fields drop-zone corresponds to the legend or series of a chart. A short practical in the
section “Pivot Chart Menus and Ribbons” demonstrates these areas of a pivot chart.
Slicers for Pivot Charts
Slicers behave the same way for both pivot tables and pivot charts. If the chart and table
are linked, they both share the same slicer. In the situation of a linked table and chart,
adding a slicer results in a shared common slicer for both the table and the chart—the
slicer is not duplicated in the worksheet.
Pivot Chart Menus and Ribbons
A pivot chart has many context menus. Right-clicking on different areas of the chart
leads to different context-sensitive pop-up menus. There are also four main ribbons
dedicated to pivot charts. Rather than list all of the context menus, and their literally
Figure 5-66 PowerPivot Field List for a pivot chart
180 Practical PowerPivot & DAX Formulas for Excel 2010
hundreds of options, one or two will be examined in the course of the following practical.
After the practical there’s a brief introduction to the pivot chart ribbons—if you don’t
have a pivot chart first, you won’t see these ribbons.
To create a pivot chart, based on an existing pivot table:
1. You can use any pivot table as the base for your pivot chart. The example here
has Sales Amount as Values, Year as Column Labels, and Country and City as
Row Labels. You can see my starting point in Figure 5-67. I have hidden the
Column Labels and Row Labels drop-down menus. It’s also filtered to show only
Argentina and Austria.
2. Make sure the focus is in the pivot table and click the PivotChart button in the
Tools group of the PivotTable Tools/Options ribbon. This opens the Insert Chart
dialog, which is shown in Figure 5-68.
3. Accept the clustered column default and click OK. The chart is added to the
current worksheet—you may want to drag it so it’s to the right of the pivot table.
If you don’t, when you remove the filter on Argentina and Austria, the chart may
obscure part of the table. For the purposes of a screenshot only, I have dragged
the chart immediately below the table—this is probably not a good position! The
pivot chart and its linked pivot table are shown in Figure 5-69.
4. Click in both the chart and the table. The field list for each has the same entries
in the drop-zones and two of the drop-zones have different captions. If you make
changes to one, the other is updated automatically. Maybe you could add a slicer
to one (for example, the Category column from the Products table, =RELATED
(Categories[CategoryName]))? The slicer appears in both field lists, but
only once in the worksheet. When you remove the slicer from one field list, it will
disappear from the other.
Figure 5-67 Pivot table for a pivot chart
Chapter 5: Pivot Tables and Pivot Charts: Overview 181
Figure 5-68 Insert Chart dialog
Figure 5-69 Pivot chart and linked pivot table
182 Practical PowerPivot & DAX Formulas for Excel 2010
5.
Right-click on a blank area of the chart and choose Select Data from the
context menu. If that option is not in the context menu, you have right-clicked
somewhere apart from a blank area! Alternatively, you can click the Select Data
button in the Data group of the PivotChart Tools/Design ribbon. Hopefully, you
are looking at the Select Data Source dialog, as shown in Figure 5-70.
6. In this dialog, click the Switch Row/Column button and then OK. Observe the
effect on both the chart and the table. This is called “pivoting”—which is why
pivot reports are called pivot reports. Repeat the previous step and this one to
revert to the original design.
7. Experiment by right-clicking on various areas of the chart. If you right-click on
a button, you can hide the buttons. Right-clicking on a blank area is a quick way
to change a chart type. Right-clicking on the chart itself allows you to format the
plot area. There are hundreds of options! Other areas where you might want
to right-click are the grid lines, the legend, the vertical axis, the horizontal axis,
and a column in the chart. All of these have one or more format options on the
context menu. These options, in turn, open dialogs with hundreds of settings
available.
8. If your chart is still looking reasonable, add Quantity from the Order Details table
to the Values drop-zone, in either field list. Some of the Quantity values are quite
difficult to see in the chart, as they are a lot smaller than the Sales Amount values.
Right-click on the values on the x-axis, and choose Format Axis. This opens the
Format Axis dialogs as shown in Figure 5-71.
Figure 5-70 Select Data Source dialog
Chapter 5: Pivot Tables and Pivot Charts: Overview 183
9. In this dialog, select the Logarithmic scale check box on the Axis Options page
and click Close. Now the Quantity values are easier to see, as the vertical scale is
logarithmic. Figure 5-72 shows the result.
10. Feel free to experiment. Maybe try changing the chart type. If you want to delete
the pivot chart, left-click to shows its borders, and press delete. The chart will be
deleted, but the original table will remain.
The next section is a very brief look at the four pivot chart ribbons and the Insert
ribbon. Only the most important and pertinent features are mentioned.
Figure 5-71 Format Axis dialog