Читать книгу Excel Power Pivot & Power Query For Dummies - Michael Alexander - Страница 32

After you define the relationships in your Power Pivot data model, it’s essentially ready for action. In terms of Power Pivot, action means analysis with a pivot table. In fact, all Power Pivot data is presented through the framework of pivot tables.

In Chapter 3, you dive deep into the workings of pivot tables. For now, dip just a toe in and create a simple pivot table from your new Power Pivot data model:

1 Activate the Power Pivot window, select the Home tab, and then click the Pivot Table command button.

2 Specify whether you want the pivot table placed on a new worksheet or an existing sheet.

3 Build out the needed analysis just as you would build out any other standard pivot table, using the Pivot Field List.

The pivot table shown in Figure 2-12 contains all tables in the Power Pivot data model. Unlike a standard pivot table, where you can use fields from only one table, the relationships defined the internal data model allow you to use any of the fields from any of the tables. With this configuration, you have a powerful cross-table analytical engine in the form of a familiar pivot table. Here, you can see that you’re calculating the average unit price by customer.

FIGURE 2-12: You now have a Power Pivot-driven pivot table that aggregates across multiple tables.

In the days before Power Pivot, this analysis would have been a bear to create. You would have had to build VLOOKUP formulas to get from Customer Number to Invoice Number, and then another set of VLOOKUP formulas to get from Invoice Numbers to Invoice Details. And after all that formula building, you still would have had to find a way to aggregate the data to the average unit price per customer.