Excel like a finance pro.
Pivot tables · Advanced
The Data Model
Lets a pivot use several related tables at once and unlocks measures written in DAX.
When to use it
The Data Model lets one pivot use several related tables at once and unlocks DAX measures and distinct counts. Relationships replace VLOOKUP helper columns.
The shape of it
- How
Check "Add this data to the Data Model" when creating the pivot, or build relationships under Data, Relationships.
Worked examples
Model pivot
Insert, PivotTable, Add this data to the Data Model → A pivot backed by the model
The entry point.
Relate tables
Data, Relationships, New: Orders[CustomerID] to Customers[ID] → Customer fields usable in an Orders pivot
No lookup column needed.
Load big data
Power Query, Close and Load To, Only Create Connection, Add to Data Model → Large data loaded to the model without a sheet
Beyond a million rows.
Worth knowing
- Power Pivot (enable the add-in) shows the model diagram and DAX editor.
- Relationships are one-to-many; the lookup table needs unique keys.
- Model pivots have no calculated fields; write measures.
Where it goes wrong
- Grouping is unavailable on model pivots; group in the source or with a calendar table.
- Files with a model are larger and slower to open.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.