📎 This article includes 1 downloadable practice file ↓
A normal pivot works on one flat table. Power Pivot (the Excel Data Model) lets a pivot use several related tables at once (sales, products, customers, calendar) without VLOOKUPs, handles millions of rows, and adds DAX measures: the same engine as Power BI, inside Excel.
In this article
1. Load tables into the Data Model
Make each table an Excel Table (Ctrl+T) with a name: Sales, Products, Calendar. Then Data › Get Data (or select the table › Power Pivot › Add to Data Model). Turn on the Power Pivot tab via File › Options › Add-ins › COM Add-ins › Microsoft Power Pivot for Excel if it isn’t visible.
2. Relationships
Power Pivot › Manage › Diagram View: drag Products[ProductID] onto Sales[ProductID], and Calendar[Date] onto Sales[OrderDate]. One-to-many, from lookup table to fact table.
3. First measures
Power Pivot › Measures › New Measure (or in the pivot field list, right-click the table › Add Measure):
Total Sales := SUMX ( Sales, Sales[Qty] * RELATED ( Products[Price] ) )
Orders := COUNTROWS ( Sales )
Avg Order := DIVIDE ( [Total Sales], [Orders] )
Computers Sales := CALCULATE ( [Total Sales], Products[Category] = "Computers" )Measures calculate for whatever the pivot shows: per category, per month, or the grand total, always correctly (ratios included).
4. Pivot from the model
Insert › PivotTable › From Data Model. Category from Products in rows, Month from Calendar in columns, Total Sales in values: three tables in one pivot, no lookups.
Power Pivot or Power BI?
The modelling and DAX are the same. Stay in Excel when reports live in workbooks and Excel users consume them; move to Power BI for interactive dashboards, sharing on the web and scheduled refresh. Everything you learn here transfers directly.
Where people go wrong
| Problem | Fix |
|---|---|
| Same total on every row | Missing relationship |
| Ratio totals wrong | Use a DIVIDE measure, not a calculated column |
| Calculated columns everywhere | Prefer measures; columns bloat the model |
The practice download has three clean tables ready for the Data Model. Next: the Power BI course.
📎 Practice files for this article
- 📗Power Pivot starter modelThree clean tables (Sales, Products, a financial-year Calendar) ready for the Data Model, with step-by-step relationships and the first DAX measures to write.⬇ XLSX · 30 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.