Excel Power Pivot: How to Create a DAX Measure in Excel

Charts & pivot tables · Updated November 21, 2026

To create a measure in Excel Power Pivot for Microsoft 365 on Windows, open the Power Pivot tab, choose Measures > New Measure, select the model table, enter a name and DAX formula, then choose Check formula and OK. For example, a measure named Total Sales can use =SUM(Sales[Sales Amount]) when the data model contains a Sales table with a Sales Amount column.

What a Power Pivot measure does

A measure is a calculation stored in an Excel workbook’s Data Model. Unlike a formula entered into a worksheet cell, a measure is evaluated when a PivotTable or another Data Model report requests a result. That means the same measure can show total sales for the entire business, a particular year, or one product, depending on the fields and filters in the report.

Measures use Data Analysis Expressions, usually called DAX. DAX has functions such as SUM, COUNTROWS, CALCULATE, and DIVIDE, and it works with model tables and columns. A measure is especially useful when a calculation must respond to PivotTable filters or be reused in multiple reports.

For instance, a total-sales measure can be placed in a PivotTable’s Values area. Put Year in Rows and the measure returns a sales total for each year; add Region as a filter and the results recalculate for the selected region. You create the measure once, rather than building a separate worksheet formula for each report layout.

Check Power Pivot and the Data Model first

Power Pivot is an Excel add-in, and its availability depends on the Excel edition and installation. On Windows, look for the Power Pivot tab on the ribbon. If it is missing, check whether the add-in is available in your installation before troubleshooting the workbook itself.

  1. Choose File > Options > Add-ins.
  2. At the bottom, set Manage to COM Add-ins and select Go.
  3. If Microsoft Power Pivot for Excel appears, select its checkbox and choose OK.

If the add-in is not listed, your Excel edition or organization’s installation may not include it; an administrator may control available features. Excel for Mac and Excel for the web do not provide the same full Power Pivot authoring workflow as Excel for Windows. For creating and editing Power Pivot measures, use a supported Windows desktop version. A workbook with a Data Model may have limits when opened in another Excel environment, so verify that the features you need are supported there.

A measure needs data in the workbook’s Data Model. You can add a table while importing or loading data, or add a normal Excel table to the model with Power Pivot’s Add to Data Model command. A PivotTable built from the Data Model is another way to confirm that the model is present. Ordinary worksheet ranges that have not been added to the model are not automatically available to a Power Pivot measure.

Create a measure from the Excel ribbon

These steps use Excel for Microsoft 365 on Windows. The labels can vary slightly between releases, but the usual ribbon route is Power Pivot > Measures > New Measure.

  1. Confirm that the table and columns you need are in the workbook’s Data Model. Use meaningful table and column names where possible; readable names make DAX easier to check later.
  2. Choose Power Pivot > Measures > New Measure. Excel opens the Measure dialog box.
  3. In Table name, select the table where you want the measure to be stored. The choice organizes the measure in the field list; it does not limit the measure to data from that table.
  4. Enter a clear Measure name, such as Total Sales. Type the DAX expression in the Formula box.
  5. Choose Check formula to catch syntax or reference problems. If the formula is valid, choose OK to save the measure.
  6. In the PivotTable Fields list, locate the measure and drag it to Values. Add fields such as Product, Month, or Region to Rows, Columns, or Filters to examine the calculation by category.

For a measure that totals a numeric column, with a Data Model table named Sales and a numeric column named Sales Amount, enter =SUM(Sales[Sales Amount]). The equals sign begins the DAX expression in the Measure dialog. If the table or column names contain spaces or punctuation, use the name as shown in the model; DAX table references with spaces are typically written in single quotation marks, as in =SUM('Sales Data'[Sales Amount]).

In some workflows, you can also create a measure from the Power Pivot window. Open the model with Power Pivot > Manage, then use the Home tab’s AutoSum menu or the calculation area to create a measure, depending on the available ribbon layout. The Excel ribbon’s New Measure dialog is often the clearest route because it presents the measure name, formula, and formatting options together.

Build and check a practical example

Suppose the Sales table contains one row per product sale, with Quantity and Unit Price columns. If the business defines revenue as quantity multiplied by price for each row, the measure should multiply those row values and add the results. For a Data Model table named Sales with numeric Quantity and Unit Price columns, use =SUMX(Sales, Sales[Quantity] * Sales[Unit Price]).

SUMX is an iterator: it evaluates the expression for each row in Sales, then sums the results. Consider three rows with Quantity and Unit Price values of 2 and 25, 3 and 10, and 1 and 40. The row revenues are 50, 30, and 40, so the measure returns 120. The measure will return a different total when the PivotTable filters the table to a subset of those rows.

If the model already has a Sales Amount column that stores the extended revenue for each row, the simpler SUM measure is appropriate. Avoid multiplying columns that have already been multiplied in the source data, or the result will be overstated. Check whether amounts are stored per unit or per transaction before choosing the expression.

For a ratio such as profit margin, first create or identify measures for profit and sales, then divide one by the other. With measures named Total Profit and Total Sales already in the model, use =DIVIDE([Total Profit], [Total Sales]). DIVIDE is generally preferable to a direct division operator because it can return a blank when the denominator is zero, rather than producing a divide-by-zero error. Set the measure’s number format to Percentage so the PivotTable displays the result as a rate.

Understand filter context and measure results

A measure’s result depends on its filter context: the set of filters active when Excel calculates it. PivotTable row and column labels, report filters, slicers, and relationships between model tables can all affect that context. This is why a measure can show one grand total and several different category totals without changing its formula.

For example, Total Sales might return 120 for the three-row example overall, 80 for rows selected by one product, and 40 for another product. The formula has not changed; the PivotTable has supplied a different context for each result. If a total looks unexpected, inspect the active filters and the relationships in the Data Model before assuming that the DAX expression is wrong.

CALCULATE is a key DAX function for changing filter context. As an illustration, with a measure named Total Sales and a Date table containing a numeric Calendar Year column, a measure for one year can be written as =CALCULATE([Total Sales], 'Date'[Calendar Year] = 2025). This expression applies a year filter while calculating Total Sales. It assumes the Date table is related appropriately to the sales data; without a useful relationship, the year filter may not affect the sales rows as intended.

Measures are not evaluated one time and stored as a value for every source row. They are recalculated as the report context changes. That differs from a calculated column, which computes a value for each row and stores that result in the model. The distinction matters for both behavior and model size.

Choose a name, format, and home table

Use names that describe the business meaning of a result, such as Total Sales, Order Count, or Gross Margin. Avoid names that are so generic that readers cannot tell what the value represents. If multiple people maintain the workbook, add a description in the Measure dialog when that field is available; a short note about units, exclusions, or business rules can prevent later confusion.

Select a number format that matches the measure. Use currency for monetary totals, a percentage for rates, and an integer format for counts. Formatting changes how a value appears, not the underlying calculation. For example, percentage formatting does not turn a value of 0.25 into a different mathematical result; it displays that value as 25%.

The selected table is the measure’s home table in the field list. It is a filing location, not a rule that the measure can only use that table. If you have many measures, placing them together in a dedicated measures table can make the field list easier to navigate, but it is optional. The measure still reads from the model tables referenced by its DAX expression.

Measures, calculated columns, and ordinary formulas

Choose a measure when the result should respond to the PivotTable’s filters and summarize data, such as total sales, average price, or margin. Choose a calculated column when you need a value for each individual row, such as a row-level category or an extended amount that must be used as a field in the model. A calculated column is not a substitute for every measure: summing a precomputed column can work, but it stores additional values and may not be needed when a measure can calculate the result directly.

Ordinary Excel formulas are often simpler for calculations confined to worksheet cells. They use worksheet cell references and are convenient for a small, fixed layout. Power Pivot measures are a better fit when data is in the Data Model, relationships connect multiple tables, and PivotTables need to reuse calculations under different filters. A worksheet formula does not automatically become a model measure simply because it sits beside a PivotTable.

There is also a difference between an explicit measure you create and an implicit aggregation Excel may add when you drag a numeric field into a PivotTable. Excel can summarize a numeric field by Sum or another available option without a named DAX measure. That can be adequate for a quick report, but an explicit measure is easier to reuse, name, format consistently, and document.

Common problems and how to fix them

  • The Power Pivot tab is missing: Check File > Options > Add-ins and whether Microsoft Power Pivot for Excel is available as a COM add-in. If it is absent, check your Excel edition or contact your administrator.
  • The table is not in the formula suggestions: Make sure it is part of the Data Model, not only a worksheet range. Open Power Pivot > Manage to inspect the model tables.
  • The formula reports an error: Confirm spelling, brackets, commas, and the exact model table and column names. DAX references columns as Table[Column]. A measure reference uses square brackets, such as [Total Sales].
  • The measure appears blank: Check whether current filters leave any matching rows, whether the source column contains usable values, and whether model relationships connect the filtered table to the fact table.
  • The grand total differs from the sum of displayed rows: Some measures are recalculated in the total’s broader filter context rather than adding the visible row results. Ratios and distinct counts often behave this way. This can be correct; assess the business meaning of the measure at each level.
  • A number looks wrong: Verify the source data’s units and the number format. A value displayed as 12% may be stored as 0.12, while a currency amount may be rounded visually without being rounded in the model.

After correcting a formula or changing model data, refresh the relevant PivotTable or model data when needed. If the source is imported, refreshing the report alone may not refresh the underlying source connection; use the workbook’s refresh options appropriate to how the data was loaded.

Frequently Asked Questions

Where do I find the New Measure command in Excel?

In Excel for Microsoft 365 on Windows, use Power Pivot > Measures > New Measure. If the Power Pivot tab is missing, check whether the Power Pivot COM add-in is available and enabled, and confirm that your Excel edition includes it.

Can I create a Power Pivot measure without a PivotTable?

Yes. A PivotTable is not required to define the measure. Create it from the Power Pivot ribbon or model window, then use it later in a PivotTable or another compatible Data Model report. A PivotTable is simply a convenient way to see how the measure responds to filters.

Why does my measure show the same total on every row?

The row field may not be filtering the table used by the measure. Check the Data Model relationships, the direction and matching columns involved, and whether the row field comes from a related table. A measure can calculate correctly while a disconnected table leaves its result unchanged across rows.

Can I use a worksheet cell reference in a DAX measure?

Not as a normal cell reference such as A1. DAX measures refer to model tables, columns, and other measures. If a value from a worksheet should participate in the calculation, bring it into the Data Model and relate it appropriately, or use a worksheet formula outside the model.

Can Excel for Mac create Power Pivot measures?

Excel for Mac does not offer the same full Power Pivot measure-authoring workflow available in Excel for Windows. If you need to create or edit DAX measures in Power Pivot, use a supported Windows desktop version of Excel. For simpler analysis on a Mac, consider PivotTables with ordinary worksheet calculations or another tool that supports the model features you need.

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.