Layout and values Updated November 2026

Data Model pivot table

Ticking Add this data to the Data Model loads the source into Power Pivot's in-memory engine, which lets one pivot use several related tables, offers Distinct Count and supports DAX measures. The cost is that calculated fields, calculated items and some grouping options are unavailable.

Syntax

Insert > PivotTable > Add this data to the Data Model; Data > Relationships; PivotTable Analyze > Fields, Items & Sets > (Measures via Power Pivot > Measures)

Part	What it is
`Relationship`	One-to-many link between a key in a lookup table and a column in the data table
`Measure`	A DAX formula such as Margin := SUM(Sales[Revenue]) - SUM(Sales[Cost]) evaluated per pivot cell
`Distinct Count`	Available only on Data Model pivots

Examples

Tables: Sales (Date, Product ID, Revenue), Products (Product ID, Category)
Relationship: Sales[Product ID] -> Products[Product ID]
Rows: Products[Category]. Values: Sum of Sales[Revenue]

Result: Revenue by category, drawn from two tables with no helper column.

Measure: Avg Order := DIVIDE(SUM(Sales[Revenue]), DISTINCTCOUNT(Sales[Order ID]))
Rows: Region. Values: Avg Order

Result: Revenue per order for each region, computed correctly in every cell including the total.

Common mistakes

Calculated Field is grayed out: Data Model pivots do not support it; write a measure instead.
Values show the same total on every row: no relationship exists between the tables, or the key columns have mismatched types.
The field list shows tables you did not expect: tick All at the top of the field list to see every loaded table.

Practice Data Model pivot table

These drills check your answer on live data, free, no account needed.

Returning customer valueCompare New and Returning customers: number of orders, total Sales and average Sales per order. Customer in rows.
Marketplace by country and yearShow total Sales from the Marketplace channel only, Country down the side and year across the top.

Questions

Should I always use the Data Model?

No. For one flat table a normal pivot is simpler and supports calculated fields. Use the Data Model for several tables, distinct counts or measures.

Is the Data Model the same as Power Pivot?

Power Pivot is the add-in window for managing the Data Model, including relationships and measures. The model itself is built into Excel 2013 and later on Windows.

Distinct countCount unique customers or products per region. In Excel tick Add this data to the Data Model and pick Distinct Count; in Google Sheets choose COUNTUNIQUE.
Pivot from multiple sheetsBuild one pivot table from several worksheets by appending them in Power Query or relating them in the Data Model. Steps for both and the old wizard limits.
Calculated fieldInsert a calculated field with Fields, Items & Sets to compute margin, commission or price per unit from other fields. Formula rules, why averages go wrong.
Text in the Values areaA pivot table cannot sum text, so the Values area shows a count. Ways to show text: put the field in Rows with tabular layout, or a Data Model CONCATENATEX.