Part V: Understanding Power Pivot and Power Query
Using Power Pivot data in reporting
Once you define the relationships in your Power Pivot data model, it’s essentially ready for
action. In terms of Power Pivot, action basically means analysis with a PivotTable. In fact, all
Power Pivot data is presented through the framework of PivotTables (PivotTables are covered
in Chapter 29, “Introducing PivotTables,” and Chapter 30, “Analyzing Data with PivotTables”).
- Activate the Power Pivot window, select the Home tab, and then click the Pivot
Table command button.
- Specify whether you want the PivotTable placed on a new worksheet or an
existing sheet.
- Build out your needed analysis just as you would any other standard PivotTable
using the PivotTable Fields list.
The PivotTable shown in Figure 35.12 contains all the tables in the Power Pivot data model.
With this configuration, we essentially have a powerful cross-table analytical engine in the
form of a familiar PivotTable. Here you can see that we are calculating the average unit
price by customer.
FIGURE 35.12
You now have a Power Pivot–driven PivotTable that aggregates across multiple tables.
In the days before Power Pivot, this analysis would have been tricky. You would have had
to build VLOOKUP formulas to get from Customers to Invoice Header and then another set of
VLOOKUPs to get from Invoice Header to Invoice Details. After all that formula building, you
still would have had to find a way to aggregate the data to average the unit price per customer.
