Excel Power Pivot: How to Add a Measure

Charts & pivot tables · Updated November 21, 2026

To add a measure in Excel Power Pivot for Microsoft 365 on Windows, open the Power Pivot tab and choose Measures > New Measure, enter a name and a DAX formula, then select OK. You can also right-click a table name in the PivotTable Fields pane and choose Add Measure; the measure can then be placed in a PivotTable like any other value field.

What a Power Pivot measure is

A measure is a named calculation written in Data Analysis Expressions (DAX). Unlike a value typed into a worksheet cell, it is stored in the workbook’s Data Model and calculated when a PivotTable or another model-based report asks for a result. Examples include total sales, average order value, profit margin, and year-to-date sales.

The key feature is that a measure responds to the current filters. If a PivotTable is showing sales by region, a total-sales measure returns a separate total for each region and a grand total. If a user filters the report to one year, the same measure recalculates for that year. You generally create a measure once and reuse it in different PivotTables and report layouts.

A measure needs data in the Data Model. A regular PivotTable based only on a worksheet range does not automatically have the model features required for Power Pivot measures. The source data must be loaded into or added to the workbook’s Data Model.

Check the Data Model and Power Pivot availability

Before creating a measure, confirm that the source table is in the Data Model and that Excel has the Power Pivot add-in available. When creating a PivotTable from a range or Excel table, use Insert > PivotTable and select Add this data to the Data Model if that option is shown. If the source table is already in the workbook model, you can proceed with it rather than creating a second copy.

If the Power Pivot tab is missing, check whether the COM add-in is enabled: choose File > Options > Add-ins, select COM Add-ins in the Manage list, choose Go, and enable Microsoft Power Pivot for Excel. The add-in’s availability depends on the Excel edition and license. If the checkbox is absent or cannot be enabled, the installed edition may not provide Power Pivot authoring.

Power Pivot’s full model and measure-authoring tools are a Windows desktop Excel feature. Excel for the web and Excel for Mac do not provide the same Power Pivot add-in workflow, so use Windows desktop Excel to create or edit measures. A workbook with a Data Model may still have different capabilities when opened in another version; check that version’s support before relying on model features.

Add a measure from the Power Pivot ribbon

  1. Open the workbook in Excel for Windows and confirm the source table is in the Data Model.
  2. Select the Power Pivot tab, then choose Measures > New Measure.
  3. In the measure dialog, choose the table where the measure should be stored. This is its home table in the field list; it does not limit which related tables the formula can use.
  4. Enter a clear Measure name, such as Total Sales. In the Formula box, enter a DAX expression beginning with an equals sign.
  5. Use Check Formula if it is available to catch syntax or reference errors. Select a number format, such as Currency or Percentage, where appropriate, and add a description if other workbook users may need context.
  6. Choose OK. The measure should appear in the PivotTable Fields pane, usually with a calculator icon, under its home table. Drag it to Values in a PivotTable.

You can instead right-click a table in the PivotTable Fields pane and choose Add Measure. That opens the measure dialog in the context of the selected table. The exact location of the command can vary slightly with the ribbon or pane layout, but it creates the same kind of model measure.

Write a simple DAX measure

Suppose a model table named Sales has a numeric Amount column and three rows with amounts of 120, 80, and 50. With those rows in Sales[Amount], enter this formula for a measure named Total Sales:

=SUM(Sales[Amount])

It returns 250 when no filters are applied. In a PivotTable split by a Region column, it calculates the sum for each region; the grand total remains the sum for the rows included in the report. The table and column names in your formula must match the names in the Data Model. If a name contains spaces or punctuation, use the name Excel displays and let the formula editor help complete references.

A useful second example is profit margin. Assume the Sales table has a numeric Profit column with values 30, 20, and 10, and the Amount values are still 120, 80, and 50. First create a measure named Total Profit using the following expression:

=SUM(Sales[Profit])

That returns 60 for the sample rows. Then create a measure named Profit Margin using this expression, with the Total Profit measure and Sales[Amount] column available in the model:

=DIVIDE([Total Profit], SUM(Sales[Amount]))

The result is 60 divided by 250, or 0.24, which is 24 percent when the measure is formatted as Percentage. DIVIDE is useful for ratios because it handles a zero denominator more safely than a direct division operator. Choose a suitable number format in the measure dialog or format the value field in the PivotTable; number formatting changes display, not the underlying calculation.

Understand filter context and relationships

Measures are evaluated in filter context: the filters supplied by the PivotTable’s rows, columns, slicers, report filters, and other model rules. That explains why one measure can show a different result for each category without a separate formula for every category. For example, putting Product in Rows and Total Sales in Values groups the same sum by product.

Relationships matter when a measure uses fields from more than one table. A typical model has a Sales fact table containing transactions and separate lookup tables for dates, products, or customers. A relationship between the lookup table and Sales lets a PivotTable filter the sales calculation by those categories. If a relationship is missing, inactive, or based on mismatched keys, the measure may show repeated totals, blanks, or results that do not respond as expected. Check the model diagram and the relationship columns when a category does not filter a measure correctly.

Measures do not simply calculate once for each visible row and then add those displayed values to obtain every grand total. The total is usually the measure recalculated over the total’s filter context. For a ratio such as profit margin, the grand total is therefore total profit divided by total sales, not necessarily the arithmetic average of the margins displayed for individual products. This behavior is often correct, but it can surprise users comparing subtotals with manually averaged percentages.

Format, rename, and manage measures

Use meaningful names that describe the business result rather than the mechanics of the formula. Total Sales is easier to identify in the field list than Measure 1. If a workbook has many calculations, choose a consistent naming style, add descriptions where available, and store measures in a logical home table. The home table is an organizational choice; the formula can refer to other tables in the Data Model when the model permits it.

Set the measure’s format to match its meaning. Currency is appropriate for revenue or costs, Percentage for a margin, and a whole-number format for counts. Avoid relying on a formula to turn a number into display text: a formatted numeric measure remains useful for sorting and further calculations. If a measure has the right value but displays too many decimal places or a currency symbol you do not want, adjust formatting rather than changing the arithmetic.

To change a measure, select its command in the model or field list and open its settings, or use the Power Pivot measure tools available in your Excel build. Update the formula or format and confirm the change. If the source data has changed, refresh the model or PivotTable as appropriate; changing a measure definition does not itself import new source rows. Save the workbook after updating important model calculations.

Common problems and how to fix them

  • New Measure is unavailable: Confirm that the Power Pivot add-in is enabled, that the workbook has a Data Model, and that the installed Excel edition supports Power Pivot authoring. A PivotTable built only from a worksheet range may need to be recreated or connected to the model.
  • The formula reports an error: Verify the table and column spelling, check that the referenced column exists in the model, and confirm that the expression uses DAX syntax. A measure formula in the dialog is an expression such as =SUM(Sales[Amount]), not an Excel cell formula such as =SUM(B2:B20).
  • The measure is missing from the field list: Look under the selected home table and expand that table in the PivotTable Fields pane. Also confirm the dialog was accepted and that you are using a PivotTable connected to the same workbook Data Model.
  • The result repeats for every category: Check whether the category table has a valid relationship to the table used by the measure. A DAX formula cannot make an incorrect or missing relationship behave like a correct one.
  • The total differs from the sum of visible rows: That can be normal for ratios, averages, distinct counts, and other calculations that are recomputed at the total level. Decide what the total should mean, then write a measure that expresses that definition.
  • New source rows do not appear: Refresh the relevant data connection and PivotTable. Check that the source range or source table includes the new records, then refresh the Data Model as needed.

Measures versus calculated columns and ordinary formulas

A calculated column computes a value for each row and stores those row-level results in the model. A measure calculates an aggregate or other result when a report requests it. For example, a calculated column might label each transaction with a year, while a measure sums transaction amounts for whatever year, product, or customer the PivotTable currently filters.

Choose a measure for totals, averages, ratios, and other values that need to respond to report filters. Choose a calculated column when you need a value on every record—for example, a category used on PivotTable rows—or when the calculation must participate in a row-level relationship or grouping. Calculated columns can increase model size because their values are stored per row; measures generally store the formula and calculate results as needed.

A regular worksheet formula is different again: it calculates in worksheet cells and usually refers to cell ranges. It can be the right choice for a small, fixed calculation, but a Power Pivot measure is designed for reusable analysis across model tables and PivotTable filter contexts. Adding a measure does not insert a visible formula into each source row.

Frequently Asked Questions

Can I add a measure without a PivotTable?
Yes. A measure belongs to the Data Model, not to one PivotTable. You can create it through Power Pivot’s Measures > New Measure command before placing it in a report. To see its result, use it in a PivotTable or another compatible model-based report.

Do I need to type the measure name in the formula?
No. Enter the measure name in the Measure name field, then enter its DAX expression in Formula. For example, name it Total Sales and use =SUM(Sales[Amount]) as the expression. Excel displays the saved name in the field list.

Can I add a measure to a normal PivotTable?
Only if the PivotTable is connected to the workbook Data Model. A PivotTable made directly from a worksheet range without the model does not use Power Pivot measures. Create or rebuild the PivotTable using the Data Model, then add the measure to its Values area.

Why does a measure show the same value for every row?
Often, the field used for the rows comes from a table that is not correctly related to the table being summed. Check that the model relationship uses the correct key columns and that the relationship is active. Also confirm the PivotTable uses fields from the intended model tables.

Can I create Power Pivot measures in Excel for Mac or Excel for the web?
The full Power Pivot add-in authoring workflow described here is for Excel on Windows. Mac and web versions do not offer the same tools for creating and editing Power Pivot measures. Use a supported Windows desktop edition to author the measure, and verify model compatibility before editing the workbook elsewhere.

Related

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.