Create a DAX Measure in Excel: Step-by-Step

How-to basics · Updated November 18, 2026

To create a DAX measure in Excel for Microsoft 365 on Windows, add your data to the workbook’s Data Model, then select Power Pivot > Measures > New Measure. Enter a measure name and a DAX formula such as =SUM(Sales[Amount]), choose a number format, and select OK. You can then add the measure to a PivotTable; unlike a formula in a worksheet cell, it recalculates in response to the PivotTable’s filters and selections.

What a DAX measure does

A measure is a named calculation stored in Excel’s Data Model. It summarizes model data when a PivotTable or another model-based report requests a result. Common uses include totals, averages, ratios, distinct counts, and calculations that combine other measures. A measure does not write a separate result into every row of a source table. Instead, it calculates a result for the current context, such as a particular month, product, or region.

For example, a total-sales measure can show one overall figure, then recalculate when you put Year in PivotTable Rows or filter the report to a single product. The measure definition stays the same; the report’s filters change the data being summarized. This makes measures useful for interactive reports and for calculations that need to remain consistent across multiple PivotTables connected to the same model.

DAX stands for Data Analysis Expressions. It is the formula language used by the Excel Data Model and Power Pivot, as well as by other Microsoft data-modeling products. DAX resembles Excel formulas in places, but it works with model tables and filter context rather than ordinary worksheet cell references.

Check that Power Pivot is available

On Windows, Power Pivot is an Excel add-in, and its availability depends on the Excel edition and organization’s license. If you do not see the Power Pivot tab, check whether the add-in can be enabled: choose File > Options > Add-ins. At the bottom, set Manage to COM Add-ins, select Go, and look for Microsoft Power Pivot for Excel. If it is listed, select its checkbox and choose OK. If it is not listed, your installed edition may not include the add-in, or your organization may control which features are available.

Power Pivot authoring is not available in Excel for the web or Excel for Mac in the same way as the Windows desktop feature described here. You can still open or interact with some workbooks and PivotTables on other platforms, but do not assume you can create or edit a Power Pivot measure there. Use the supported Windows desktop version to build the model and create the measure.

Put your data in the Data Model

A DAX measure needs model data to summarize. If your source is a clean rectangular range, select a cell in it and choose Insert > Table, confirming that the table has headers if appropriate. Give the table a useful name on the Table Design tab, such as Sales. Clear names make DAX formulas easier to read and reduce confusion when the workbook contains several tables.

To create a PivotTable from a table and include that table in the model, select a cell in the table and choose Insert > PivotTable. In the dialog, select the option to add the data to the Data Model, then create the PivotTable. Another route, when available in your Excel build, is to select a cell in the table and use Power Pivot > Add to Data Model. The exact options can vary with the ribbon and workbook state.

If your analysis uses more than one table—for example, sales transactions plus separate Products and Calendar tables—add the tables to the Data Model and create appropriate relationships. A measure can summarize data across related tables when the model relationships and filter directions support the calculation. A measure cannot repair an incorrect relationship, so confirm that your model connects tables using the intended key columns.

Create the measure with the ribbon

Once the relevant table is in the Data Model, use this workflow in Excel for Microsoft 365 on Windows:

  1. Select a cell in a PivotTable that uses the Data Model, or open the model using Power Pivot > Manage.
  2. Choose Power Pivot > Measures > New Measure. In some ribbon layouts, the command appears in the Calculations group under Measures.
  3. In the New Measure dialog, select the table where you want the measure stored. This is an organizational choice; measures are available to PivotTables using the model regardless of which model table stores them.
  4. Enter a descriptive Measure Name, such as Total Sales. In the formula box, enter a DAX expression beginning with an equals sign.
  5. Use Check Formula if the dialog offers it, select an appropriate category and number format, and choose OK.

A measure’s name is entered separately from its formula in this dialog. For example, give it the name Total Sales and use the formula =SUM(Sales[Amount]). The formula assumes the Data Model contains a table named Sales and a numeric column named Amount. If your table or column has a different name, substitute the exact model name. Column references use square brackets; table names that contain spaces may need single quotation marks in DAX references.

You can also manage measures in the Power Pivot window. The calculation area at the bottom of a table view can display measures and let you edit them, depending on the window layout. The ribbon’s New Measure dialog is often the clearest starting point because it provides fields for the measure name and formatting as well as the expression.

Use a measure in a PivotTable

After you save the measure, find it in the PivotTable Fields pane. It usually appears under the table where it was stored, often in a measures or values area. Drag it into Values, then add fields such as Year, Region, or Product to Rows, Columns, or Filters. The measure’s output updates as you change the PivotTable layout and filters.

If a field list does not refresh, click inside the PivotTable and use PivotTable Analyze > Refresh, or choose Data > Refresh All to refresh workbook connections and model data. A measure is not a worksheet formula that fills down, so you will not see its result beside every source row. To see row-level results, use a calculated column or a worksheet formula when that is the actual requirement.

For instance, a total-sales measure placed in Values with Year in Rows produces a sales total for each year. Adding Region to Columns divides those totals by region as well. The measure itself does not need separate formulas for each year or region; the PivotTable supplies the context in which the measure is evaluated.

Write useful DAX measures

Start with a simple aggregation and build up only when a report needs more logic. These examples assume the Data Model contains a Sales table with the named numeric columns. Enter each expression as the formula for a measure in the New Measure dialog.

  • For a measure named Total Sales, with a numeric Amount column in Sales, use =SUM(Sales[Amount]). If the model’s Amount values total 12,500 for the current PivotTable selection, the measure returns 12,500.
  • For a measure named Total Cost, with a numeric Cost column in Sales, use =SUM(Sales[Cost]).
  • For a measure named Gross Margin, after creating the two measures above, use =[Total Sales]-[Total Cost]. If current-context sales are 12,500 and costs are 8,000, the result is 4,500.
  • For a measure named Margin %, after creating Gross Margin and Total Sales, use =DIVIDE([Gross Margin],[Total Sales]). With those example figures, the result is 0.36, or 36% when formatted as a percentage. DIVIDE returns a blank when the denominator is zero or blank unless you supply an alternate result.
  • For a measure named Customer Count, with customer identifiers in Sales[CustomerID], use =DISTINCTCOUNT(Sales[CustomerID]). This counts distinct IDs in the current filter context, not transaction rows.

Choose the measure’s number format when you create it, or adjust its format in the measure settings in Power Pivot. Use a currency format for monetary totals and a percentage format for ratios; otherwise, a valid ratio such as 0.36 may display as a decimal rather than 36%. Formatting affects how a result is shown, not the underlying calculation.

Measures versus calculated columns and worksheet formulas

A calculated column evaluates an expression for each row in a model table and stores a value for that row. A measure calculates an aggregate or other result when a report asks for it. Use a calculated column when you need a row-level attribute such as a label or a per-row category that can be placed in a PivotTable’s Rows or Columns. Use a measure for totals, averages, ratios, and other results that should respond to report filters.

A regular worksheet formula is different again: it refers to cells or ranges and calculates in the worksheet grid. It can be the simplest choice for a small calculation beside ordinary data. A DAX measure is most helpful when the calculation belongs to the Data Model and should respond consistently to PivotTable context. A common misconception is that a measure is just a formula stored in a cell. It is not; it is part of the model and is used through model-aware reports.

Older PivotTables also offer calculated fields in some situations. These are not interchangeable with DAX measures: calculated fields have different limitations and do not provide the full DAX measure behavior of the Data Model. If your PivotTable was built from a Data Model, create a measure rather than expecting a traditional calculated field to work the same way.

Common problems and how to fix them

  • The Power Pivot tab is missing. Check File > Options > Add-ins and the COM Add-ins list. If Power Pivot is not available there, check your Excel edition and organization’s installation or licensing.
  • The formula reports an unknown table or column. Confirm that the source table is in the Data Model and that the names in the expression match the model. A worksheet range that has not been added to the model cannot be referenced as a model table.
  • The measure is missing from the field list. Confirm that it was saved in the correct workbook model and refresh the PivotTable. Also check whether the field list is showing the table that stores the measure.
  • The result is repeated or unexpected. Inspect the PivotTable’s filters, relationships, and the fields used in Rows and Columns. Measures respond to filter context, so an unexpected total may reflect the model’s relationships or the current report layout rather than a typing error.
  • A percentage displays as a decimal. Set the measure’s format to Percentage. For example, 0.36 is the same numeric value as 36% when displayed with percentage formatting.
  • A division returns an error or an unhelpful value. Use DAX DIVIDE rather than the division operator when a denominator may be zero. Decide whether a blank or an explicit alternate result is appropriate for the report.

Frequently Asked Questions

Can I create a DAX measure directly in an Excel cell?
No. A DAX measure belongs to the workbook’s Data Model and is created with Power Pivot or the model’s measure tools. If you need a cell-by-cell calculation in a worksheet, use a regular Excel formula instead.

Do I need Power Pivot to create a measure?
For the Excel workflow described here, you need the Data Model and a supported measure-creation interface, commonly provided by the Power Pivot add-in on Windows. Availability depends on your Excel edition and setup. If the add-in is absent, confirm your version and license rather than assuming every Excel installation includes it.

Can I use a DAX measure without a PivotTable?
A measure is evaluated when a model-aware report or calculation requests it. In Excel, PivotTables are the usual way to display and explore measures. A measure does not automatically populate a worksheet range like a standard cell formula.

Why does my measure show the same value on every row?
That can happen when the row field does not filter the table being summarized, often because a relationship is missing, inactive, or built on unsuitable columns. Check the model relationships and confirm that the PivotTable uses fields that filter the measure’s data.

Should I use SUM or SUMX?
Use SUM to add the values in one model column. SUMX iterates over a table and evaluates an expression for each row before aggregating, so it is useful for calculations that cannot be represented by summing one existing column. Start with SUM when it expresses the calculation correctly; use an iterator only when the row-by-row expression is needed.

Choosing a good measure name

Clear naming helps people understand a PivotTable field list without opening the formula. Prefer names such as Total Sales, Gross Margin, or Distinct Customers over generic labels such as Measure 1. Avoid naming a measure exactly like a source column if that could make the field list confusing. If the workbook has many measures, group related ones in an appropriate model table and use consistent terms for units and percentages.

Finally, validate a new measure against a small, understandable slice of data. Compare its result with a hand-checkable subtotal or a known source total, and test how it changes when you apply a filter. This helps catch model issues early and confirms that the measure answers the intended question rather than merely producing a plausible-looking number.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.