July 19, 2014 Leave a comment
One of the interesting things to see when working with Pivot tables is to look at the differences between regular pivot tables that Excel has been able to create since about 1997 with Excel 97 (actually Excel 5 in1993 had basic pivot table functionality, but no wizard yet) and Power Pivot tables which has been a relatively recent addition (Excel 2010 with an add-in). Standard pivot table functionality still exists in Excel 2013 and serves as an alternatively tool for simple pivot tables that only require a single data source and less than a million rows of data.
In regular pivot tables, you might want to create a pivot table and use one of the dimensions as a filter. In the following example, I use a version of the FactSales table ripped from Contoso and I load it directly into Excel as one of the worksheets. Then I click on the Pivot Table command from the Insert ribbon to create a basic pivot table.
I added the Sales Amount field to my Values area. Being a numeric value, Sales Amount makes a good choice as a measure for a pivot table. I then added the dimensions for store and product as my horizontal and vertical dimensions generating a reasonable pivot table. However, I also added channel as the filter.
By default, after adding a filter, the pivot table still displays all values for the filter in the table. However, by using the dropdown, I can view the pivot table with one or more of the filter values at a time.
With only four possible values, it would not seem to be a big deal to view each of the channels one at a time. However, Excel provides another way to display the pivot table showing the pivot table on a series of pages with each page representing a different filter value. To do this, I can go to the PivotTable Tools menu group and select the Analyze ribbon. Then select Show Report Filter Pages… from the Options dropdown menu as shown in the image below.
When I select this option, Excel displays a dialog that let me pick the filter I want to expand. In this case, I only have a single filter on the Channel field so I select that filter and click the OK button.
Excel then generates a separate worksheet for each of the filter values and labels the worksheet tab with the filter value so you can easily click on a table to view the pivot table with the selected filter value applied.
This option makes it easier to go back and forth between views of the pivot table with different filter values applied. If you give a copy of the workbook to someone who might not be as familiar with pivot tables as you are, you can expand out the pivot table by the filter values and then lock the workbook so they cannot accidentally make changes to it. It also makes it easier to simply print a series of the pages to your printer if hardcopy is necessary.
So using the same data, I attempted to add the original data to the Power Pivot Data Model by using the Add to Data Model option in the PowerPivot ribbon
I then defined a Pivot Table from the data model using the same fields as before. When I then opened the Options dropdown menu from the Analyze ribbon of the PivotTable Tools group as I did before, I was surprised by the fact that the option to Show Report Filter Pages was greyed out as you can see below.
Apparently, this option is not available when displaying the data through the Power Pivot model, but is available from the basic Pivot table model. I’m sure there is a way to brute force some code to expand each filter value into a table in a new worksheet and rename that worksheet appropriately, but that kind of defeats the purpose of creating a “BI Tool for the Masses” which is the goal of most of my Pivot Table blogs.
C’ya next time.