Normal pivot tables work from one table. Power Pivot lets you load several tables, connect them, and create your own calculations called measures, written in DAX.
Step 1: the data model
Add your tables to the Data Model, then connect them with relationships: for example, a Sales table connected to a Products table by Product ID. Now one pivot table can use fields from both, with no VLOOKUP.
Step 2: calculated columns and measures
- A calculated column is calculated row by row and stored in the table.
- A measure is calculated on the fly from what the pivot table shows. Totals, ratios and KPIs are usually measures.
A first measure
Total Sales = SUM(Sales[Amount])
Once you create it, drag it into a pivot table and it recalculates for every row, column and filter.
CALCULATE and filter context
CALCULATE is the most important DAX function. It changes the filters before calculating. For example, sales for one region only:
North Sales = CALCULATE([Total Sales], Sales[Region] = "North")
Filter context means the pivot table's rows, columns and slicers decide which rows each measure sees. Understanding this is the key to DAX.
Time intelligence
With a proper date table, DAX can calculate year to date, same period last year and growth percentage in a few lines.
Go deeper
Our Advanced Excel course covers the data model, calculated columns, DAX measures, CALCULATE and time intelligence in chapter 5.