Microsoft Excel · Advanced modeling and platform-dependent features · Intermediate
Create and audit a basic Power Pivot measure
Create an explicit DAX sum measure and audit filter context, source changes, and the distinction from calculated columns.
Official documentation reviewed; synthetic fixtures checked independently; not executed in Excel · Editorial standards
Quick answer: Create an explicit measure such as Total Amount:=SUM(Sales[Amount]) in Power Pivot’s Calculation Area. Put that measure in a model PivotTable’s Values area, then test its grand total and filtered subtotals. A measure is evaluated in the report’s context; it is not a value stored separately on every source row.
Prepare a simple model
Use English Excel for Microsoft 365 desktop on Windows with the Power Pivot tab already available. This guide assumes access to the feature; it does not promise Power Pivot authoring in Mac, web, or every perpetual edition. Use a local, disposable workbook and synthetic amounts.
Enter this block at A1:C5 and convert it into an Excel table named Sales:
Region Channel Amount
North Online 100
North Retail 50
South Online 80
South Retail 20
Make sure Amount contains numbers. The expected total is 250. Select the table and choose Power Pivot > Add to Data Model. In Power Pivot > Manage, inspect the Sales table; verify that all four records are present before adding calculations.
Create a named explicit measure
- In the Power Pivot window, select Sales.
- If the Calculation Area is hidden, choose Home > View > Calculation Area.
- Select an empty cell in that area, not a cell in the body of a data column.
- In the formula bar, enter
Total Amount:=SUM(Sales[Amount])and press Enter. - Inspect the result and give the measure a readable numeric format. Keep the name distinct from the Amount column.
The name before the colon identifies the measure. The formula aggregates the Amount column; it does not refer to a worksheet address such as C2:C5. The table name must match your model table exactly. This is an explicit measure that can be reused by other PivotTables in the workbook, unlike merely dragging a column into Values to create a report-specific implicit aggregation.
Audit the measure in a report
From Power Pivot, choose Home > PivotTable, place it on a new worksheet, and confirm. Put Region in Rows, Channel in Columns, and Total Amount in Values.
| Region | Online | Retail | Total |
|---|---|---|---|
| North | 100 | 50 | 150 |
| South | 80 | 20 | 100 |
| Total | 180 | 70 | 250 |
These numbers provide independent checks for row-group and column-group filter context, and the grand total. Filter Channel to Online: North should show 100, South 80, and the visible total 180. Clear that filter after checking.
Do not add a worksheet SUM around a displayed grand total and then call it a DAX measure. Likewise, a calculated column is used for per-row results and can serve as a category; it is not the same tool as this report-context aggregation.
Change the source and inspect persistence
Change the South/Retail amount from 20 to 30 in the original Excel source table. Update/refresh the model and the PivotTable. South becomes 110, Retail becomes 80, and the overall total becomes 260. North remains 150. The measure definition should still be SUM(Sales[Amount]); you should not need to edit its formula for a changed source number.
Save as XLSX and reopen. Check the measure in the Calculation Area and the report’s 260 total. If a value remains stale, inspect model refresh before changing a correct DAX formula. If the measure is missing from the field list, verify the report is based on the model and that you created an explicit measure in the intended table.
For the underlying aggregation concept see SUM, but remember that DAX’s table-column reference and report filter context differ from a worksheet SUM over a fixed cell range.