Downloads

Power Pivot in Excel

The Excel Data Model: load data, relate tables, write measures, PivotTables and cube formulas.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Load data into the Excel Data Model and relate the tables.
  • Write measures in DAX and use them in PivotTables.
  • Use cube formulas and a DAX query table to check your numbers.
  • Refresh and share a workbook built on the Data Model.

Power Pivot is the DAX engine inside Excel for Windows: it holds many tables with relationships in the Data Model, and PivotTables, PivotCharts and cube formulas read from it. It uses the same DAX as Power BI, with a smaller feature set. Availability: Microsoft 365 Apps for Windows and Excel 2016/2019/2021 Professional Plus editions; not in Excel for Mac or the web (Mac can use a model built on Windows but cannot create one). If the Power Pivot tab is missing: File > Options > Add-ins > Manage COM Add-ins > Go > tick Microsoft Power Pivot for Excel.

Getting data into the Data Model

Sales Excel table Customers Power Query Calendar date table Excel Data Model (Power Pivot) Customer one row each Sales many rows Date one row a day Measure: Total Sales = SUM(Sales[Amount]) PivotTable on the model Region Total Sales East 412,300 West 298,100 Year slicer
The Excel Data Model holds the tables and their relationships. PivotTables and slicers read from it.

Relationships and the model diagram

Power Pivot > Manage > Home > Diagram View: drag the key column of the fact table onto the key column of the dimension. Or Data > Relationships (Data > Relationships > New). Excel's relationships are one-to-many with a single filter direction (many-to-one fact to dimension); bidirectional and many-to-many cardinalities are limited compared with Power BI. Create relationships on integer keys, one active relationship between two tables.

Date table

Power Pivot > Design > Date Table > Mark as Date Table, choose the date column, which must contain unique, contiguous dates. Build the calendar in Power Query (see the patterns topic) or use Date Table > New to have Excel create one. The DAX time intelligence functions then work.

Writing measures (the right way in Excel)

Features Power BI has that Power Pivot does not (or hides)

Verify against your Excel build, because Microsoft adds to Excel's Data Model over time; as a guide: - No New Table (calculated tables) command in Power Pivot. Workarounds: create the table in Power Query (the Calendar, Top N list), load a linked Excel table, or define a DAX query table (below). - No calculation groups, field parameters, or user-defined functions created in the Excel UI. Time intelligence variants are separate measures (YTD, PY, YoY %). - No row-level security in Excel's Data Model (security belongs to Power BI, Analysis Services or the file's sharing). - No composite models, DirectQuery or incremental refresh: it is import only. - Visuals are PivotTables and PivotCharts (plus slicers, timelines, conditional formatting, sparklines, and 3D Maps), not the Power BI visual library. - Many-to-many and bidirectional relationships are limited, so model as a star schema and use TREATAS or bridge tables in DAX. - Tools like DAX Studio connect to Excel's Data Model (open the workbook, then connect) to run DAX queries and measure performance, and Tabular Editor can edit the embedded model through its Excel connection with caution (back up first).

PivotTables on the Data Model

Insert > PivotTable > Use this workbook's Data Model. In the field list choose All, then drag dimension columns to Rows/Columns/Filters and measures to Values. Slicers (PivotTable Analyze > Insert Slicer) connect to every PivotTable that shares the Data Model (Report Connections). A timeline slicer needs a date column. Show Values As (% of Grand Total, Running Total, Difference From) is a PivotTable feature, not DAX; use DAX when you need the same result in any context. Format a measure once in the measure definition (Format), not in the PivotTable's Value Field Settings, so every PivotTable shows it identically.

Cube formulas (CUBEVALUE, CUBEMEMBER)

Convert a PivotTable to formulas (PivotTable Analyze > OLAP Tools > Convert to Formulas) to lay out a pixel-perfect report. Examples against the Data Model connection ThisWorkbookDataModel:

=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total Sales]", "[Calendar].[Year].&[2017]", "[Channel].[Channel].&[Online]")
=CUBEMEMBER("ThisWorkbookDataModel", "[Channel].[Channel].&[Online]")
=CUBERANKEDMEMBER("ThisWorkbookDataModel", "[Product].[Product].[Product].Members", 1)

Member keys use the ampersand form [Table].[Column].&[Value], measures are [Measures].[Name]. Referencing cells (slicer selections via CUBESET) makes the report interactive.

Query the Data Model with DAX (a DAX query table)

Data > Existing Connections > Tables tab > pick any table in the Data Model > Open > choose Table. Then Table Tools > Design > Properties (or Data > Queries & Connections > right-click the connection > Properties > Definition): set Command Type to DAX and Command Text to a query such as:

EVALUATE
SUMMARIZECOLUMNS('Calendar'[Year], Product[Brand], "Sales", [Total Sales], "Margin %", [Gross Margin %])
ORDER BY 'Calendar'[Year], [Sales] DESC

The result lands in a worksheet table that refreshes with the model, a way to get a DAX-calculated table in Excel without New Table.

Refresh and sharing

Data > Refresh All refreshes Power Query queries and the Data Model. Save the connection credentials (Data > Queries & Connections > Properties > Definition). Sharing: send the workbook (the Data Model rides inside it) or publish to OneDrive/SharePoint; Excel on the web can open and refresh-view PivotTables built on a Data Model but cannot edit the model (check your tenant for current behavior). Large models: reduce columns and rows, avoid wide text columns, load only needed tables.

Typical first lesson in Excel (Contoso)

  1. Data > Get Data > From SQL Server, server learnsql.cpetoday.com,1433, database contoso, tables FactSales, DimDate, DimProduct, DimProductSubcategory, DimProductCategory, DimStores, DimChannel; Transform Data, keep the columns you need, Close & Load To > connection only, Add to Data Model.
  2. Power Pivot > Manage > Diagram View: relate FactSales to the dimensions.
  3. Mark the calendar table as date table.
  4. Add measures Total Sales := SUM(FactSales[SalesAmount]), Total Cost, Gross Margin, Gross Margin %.
  5. Insert > PivotTable from the Data Model: Category on rows, Year on columns, Total Sales in values.
  6. Add Sales YTD = TOTALYTD([Total Sales], DimDate[Datekey]) (use your date table's column) and Sales PY. Name the Power Query steps (rename Source, Navigation) for clarity and keep keys as whole numbers.

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.