Power Pivot Measures: How to Create and Use Them in Excel

Power Pivot measures are calculations written in DAX that summarize data in response to a PivotTable’s current filters, rows, and columns. In Excel for Microsoft 365 on Windows, create one from the Power Pivot tab by choosing Measures > New Measure, entering a formula such as =SUM(Sales[Amount]), and adding it to a PivotTable.

What a Power Pivot measure is

A measure is a named calculation stored in the Excel Data Model. Instead of calculating a separate result for every row in a source table, it evaluates an expression when a report asks for a value. Put the measure in a PivotTable’s Values area, then use fields such as date, product, or region to break down the result.

For example, a Total Sales measure can return overall sales when the PivotTable has no other fields, sales by product when Product is on Rows, or sales for a selected month when a date filter is active. The expression stays the same; the filters change the data included in its result. This makes measures useful for reusable totals, averages, ratios, and business metrics.

Power Pivot is Excel’s data-modeling technology. It can relate multiple tables and analyze data without requiring all report calculations to be placed in worksheet cells. Measures are particularly valuable when a PivotTable needs a calculation that should update correctly across different groupings.

Measures versus calculated columns

A measure is evaluated on demand in the context of a report. A calculated column is evaluated for each row in a model table and stores a value for that row. That distinction affects both behavior and model size. A row-level label such as a band or category may be appropriate as a calculated column; a total that changes with PivotTable filters is generally a measure.

Suppose a Sales table contains individual transactions. A calculated column could compute a value for each transaction, while a measure could add those values for whatever date or product the report currently displays. Measures do not create a stored result for every transaction, and they can aggregate differently at each level of a PivotTable.

Measures also differ from ordinary PivotTable aggregations. Excel can automatically sum a numeric field added to Values, but that is an implicit aggregation, not a named DAX measure. A defined measure is explicit, reusable across PivotTables, and easier to identify and format consistently. For important report metrics, explicit measures make the calculation more visible and manageable.

How filter context changes a result

The central idea behind measures is filter context: the set of filters currently affecting the calculation. PivotTable rows and columns, report filters, slicers, and connected timelines can all contribute. If a PivotTable displays sales by category, the category on a row filters the model to that category before the measure is evaluated.

This explains why the same measure can show different values in different parts of a report. A grand total is calculated for the overall set of included rows; it is not necessarily the sum of displayed percentages or averages. For example, a total margin percentage should normally be calculated from total margin divided by total sales, rather than by adding each category’s margin percentage.

DAX also has row context, which is most directly associated with calculated columns and iterating functions. A basic measure such as SUM generally relies on filter context. More advanced expressions can change or create context, which is why DAX measures sometimes produce results that seem surprising if they are treated like ordinary worksheet formulas.

Create a measure in Excel

First, add the relevant data to the Excel Data Model. When importing data, select the option to add it to the Data Model, or use Power Pivot to add a table. A PivotTable can also be created from the model using Insert > PivotTable and selecting the workbook’s Data Model as its source.

Select a cell in a PivotTable based on the Data Model.
On the ribbon, choose Power Pivot > Measures > New Measure. Depending on the ribbon layout, the Measures command may appear in the Calculations group.
In the New Measure dialog, choose the table in which to store the measure, enter a measure name, and enter its DAX formula.
Choose a number format appropriate to the result, such as Currency, Percentage, or a whole number, and confirm the dialog.
Find the measure in the PivotTable Fields list and add it to Values. Add relevant dimensions to Rows, Columns, Filters, or slicers to examine the result.

If the Power Pivot tab is missing, check whether the add-in is available and enabled: File > Options > Add-ins, select COM Add-ins in the Manage box, choose Go, and enable Microsoft Power Pivot for Excel if it appears in the list. Availability can depend on the Excel edition and organizational setup. Excel for the web does not provide the full Power Pivot authoring experience; use Excel desktop to create or edit these measures.

Basic DAX measure examples

Assume the model contains a Sales table with three transaction rows. The Amount values are 120, 80, and 200, for a total of 400. The Cost values are 70, 50, and 130, for a total of 250. These figures are illustrative and help show how a measure responds to the model.

With a table named Sales and transaction amounts in Sales[Amount], create a measure named Total Sales with this formula. It returns 400 for all three sample rows and will recalculate when filters change.

=SUM(Sales[Amount])

With the same three transaction rows and the Total Sales measure already created, use this formula for Average Sale. It returns 133.33 when displayed to two decimal places, because 400 divided by 3 is 133.333… .

=DIVIDE([Total Sales], COUNTROWS(Sales))

With Sales[Cost] holding 70, 50, and 130 for those transactions, create Gross Margin using the next formula. It returns 150: total sales of 400 less total cost of 250. If the PivotTable filters to one product, both totals are recalculated for that product.

=[Total Sales] - SUM(Sales[Cost])

With the Gross Margin measure available and Total Sales equal to 400, create Margin Percentage as follows. It returns 37.5%, since 150 divided by 400 is 0.375. Format the measure as Percentage rather than multiplying by 100 in the formula.

=DIVIDE([Gross Margin], [Total Sales])

DIVIDE is often preferable to the division operator for ratios because it handles a zero denominator without returning a divide-by-zero error; by default, it returns a blank in that situation. You can supply an alternate result as a second argument if the report should show a different value, but decide deliberately whether zero, blank, or another display is meaningful.

Using CALCULATE and filter changes

Some measures need to modify the filters applied to a calculation. DAX’s CALCULATE function evaluates an expression in a modified filter context. One common use is to compare a category’s value with the overall total, while removing the category filter for the denominator.

Assume the Sales table has a Category column and the sample 400 in sales consists of 200 for Office and 200 for Home. With Total Sales defined, create a measure named Sales Share using this formula. In an Office row it returns 50%, because Office sales of 200 are divided by all-category sales of 400; the Home row also returns 50%.

=DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales[Category])))

ALL removes the filter from the specified category column for the denominator. Other filters, such as a selected year, can remain active. That means the result is the category’s share within the other selected conditions, rather than necessarily its share of all data in the workbook. If the intended comparison is different, the filter-removal logic must be adjusted.

Functions such as CALCULATE and ALL are powerful but can make a measure harder to interpret. Build and validate a simple base measure first, then layer the filter logic on top. A short descriptive name and a useful format also make a measure easier for other PivotTable users to understand.

Formatting, organization, and good practice

Choose the format in the measure dialog or adjust it through the model’s measure settings. Use Currency for monetary amounts, Percentage for ratios, and a sensible number of decimal places for averages. Formatting changes how a value is displayed; it does not change the underlying calculation. A percentage measure should normally return a decimal fraction such as 0.375 and rely on percentage formatting to display 37.5%.

Use names that describe the meaning and units of the result, such as Total Sales, Average Sale, or Margin Percentage. Avoid names that are indistinguishable from source columns. In a larger model, consider creating a dedicated measures table to keep measures together; this is an organizational choice and does not change their calculation.

Use a base measure for a frequently reused total, then reference it in related measures.
Set the measure’s number format instead of embedding display characters in the result.
Check grand totals as well as individual rows, especially for averages, percentages, and ratios.
Test a measure with a small, independently calculated sample before relying on it in a larger report.
Keep table and column names accurate, and confirm that model relationships connect the tables used by the PivotTable.

Common problems and troubleshooting

A measure that returns blank may have no rows in its current filter context, may be affected by a relationship issue, or may use a denominator that is blank or zero. Check slicers and report filters first, then verify that the relevant tables are related and that the measure references the intended columns.

If a number repeats down every row instead of changing by product or region, the expression may be removing a filter, or the field used to group the report may come from a disconnected table. Review ALL and other filter-modifying functions, and inspect the model relationships. A repeated result is not automatically an error: a measure designed to show an overall benchmark can correctly repeat across categories.

If the total does not equal the sum of visible rows, determine whether the measure is additive. Sales amounts are usually additive across categories, but averages and percentages generally are not. The grand total for an average is recalculated from all underlying rows; it is not normally the arithmetic mean of the displayed category averages. Similarly, a margin percentage total should use total margin divided by total sales.

Errors about a missing name or column often come from a typo, a renamed model field, or using a worksheet range reference in DAX. Measures refer to Data Model tables and columns, for example Sales[Amount], rather than worksheet addresses such as B2:B20. Confirm the table name in the PivotTable Fields list and use the model field names exactly.

Frequently Asked Questions

Are Power Pivot measures written in DAX?
Yes. DAX, or Data Analysis Expressions, is the formula language used for Power Pivot measures and other Data Model calculations. A measure begins with an equals sign in its formula box and refers to model tables, columns, and measures.

Do measures update when I use a slicer?
Yes, if the slicer is connected to the PivotTable and its field can filter the relevant model data. The measure is recalculated under the resulting filter context. A disconnected slicer or missing relationship may not affect it as expected.

Can I use a measure in a normal worksheet cell?
A measure is designed for Data Model reports, especially PivotTables and related model features. It is not a regular worksheet formula that you enter into a cell and fill down. To return model values to worksheet cells, use a suitable PivotTable layout or a supported cube function.

Why is my measure not visible in the PivotTable Fields list?
Confirm that it was created in the intended workbook Data Model and that the PivotTable uses that model. Refresh the PivotTable if needed, and look under the table selected when the measure was created. Also check that the Power Pivot add-in is available and enabled.

Should I use a measure or a calculated column for a ratio?
Use a measure when the ratio should respond to report filters and be recalculated from aggregated values, such as total margin divided by total sales. Use a calculated column only when the result is inherently a stored value for each individual row and that row-level value is what the report needs.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.