- Preparing Your Data for PowerPivot
- Getting Your Data into PowerPivot
- Define Relationships
- Add Calculated Columns Using DAX
- Build a Pivot Table
- World-Class Data Compression
- Asymmetric Reporting with PowerPivot
This chapter is from the book
This chapter is from the book
This chapter is from the book
Asymmetric Reporting with PowerPivot
Asymmetric reporting is not possible with pivot tables that are built from pivot caches of Excel data. In Figure 2.33, a small data set in A1:D37 is used to produce the pivot table in columns G:N.
)
Figure 2.33 Delete those five columns from the report.
Out of seven data columns in the report, you don't want to see five of them. You are interested in last year's actuals and this year's forecast. This is not really possible with a regular pivot table.
You could use PivotTable Tools Options, Options, Totals & Filters, and then clear Show Grand Total For Rows to get rid of the grand total in column N.
Then, you can click a year heading in the pivot table, use PivotTable Tools Options, Field Settings and select None for the Subtotals to get rid of two more columns. But then, you are left with no good way to delete 2009 Budget or 2010 Actuals. If you try to filter out the Budget, it will be removed from both years. If you try to delete column I, you will be met with the warning that you cannot Move a Part of A Pivot Table report message (see Figure 2.34).
)
Figure 2.34 You can get close using regular pivot table tools, but then you are shut down.
Option 1 is to use Home, Format, Column, Hide to hide the columns that you don't want to see. Option 2 is to convert the pivot table to values using Copy and then Paste, Paste Values.
With PowerPivot, a third option becomes available. Because data in the PowerPivot window becomes an online analytical processing (OLAP) cube, you have access to OLAP tools, including named sets.
Follow these steps to create an asymmetric report:
- Open your regular data set in Excel.
- Select one cell in the data and press Ctrl+T. Confirm that your data has headings. Click OK. The data is formatted and a new Table Tools Design tab appears. On the left side, the Table Name appears as Table 1. Type a new name, such as Financials.
- On the PowerPivot tab, select Create Linked Table.
- In the PowerPivot Window, open the Pivot Table drop-down and select Pivot Table.
- Choose a location for the pivot table.
- Select State and Revenue from the Pivot Table Field List. Drag Year and Measure to the Column Labels drop zone.
- You now have a pivot table that looks identical to the one in Figure 2.33. The difference is that Named Sets are not grayed out anymore on the Options tab.
- Go to PivotTable Tools Options. Open the Fields, Items, and Sets drop-down. Select Create Set Based on Column Items.
- Type a set name, such as BudAct. The Display Folder can be blank. Excel shows you the 7 columns currently in your pivot table.
- Select the row for FY2009 Budget, as shown in Figure 2.35. Click Delete Row.
Figure 2.35 Select a pair of values and click Delete.
- Repeat step 10 for the other four rows that you don't want to show in the pivot table. When you are done, you should see only two rows, as shown in Figure 2.36.
Figure 2.36 Repeat for each item that you want to delete.
- Click OK to complete the set.
)
)
As shown in Figure 2.37, you will now have a pivot table that will report asymmetric sets of columns.
)
Figure 2.37 This pivot table would be very difficult to create without PowerPivot.
Next Steps
This chapter showed you what is possible with pivot tables. In the next chapter, you will see both the benefits of the drawbacks of using PowerPivot.