DAX Formula in Pivot Table: Measures and Examples

A DAX formula in an Excel PivotTable is usually a measure: a calculation stored in the workbook’s Data Model that responds to the fields and filters in the report. To create one in Excel for Microsoft 365 on Windows, add your data to the Data Model, then use Power Pivot > Measures > New Measure and enter a DAX expression such as =SUM(Sales[Amount]).

What DAX in a PivotTable means

DAX, short for Data Analysis Expressions, is the formula language used by Power Pivot and the Excel Data Model. It is designed for calculations over related tables and for results that change as a PivotTable is filtered, grouped, or rearranged. A regular Excel PivotTable can sum or count a field using its built-in summary options; a DAX measure lets you define a reusable calculation beyond those standard summaries.

The key distinction is that DAX is not a formula typed into a PivotTable cell. A measure belongs to the model, not to an individual cell. When you place a measure in the Values area, Excel evaluates it separately for each visible combination of row, column, slicer, and filter selections. The same measure can therefore show a different result by month, product, or region without you writing a separate formula for each group.

A DAX measure requires a PivotTable connected to the Data Model. If your PivotTable was created from a simple worksheet range without adding that data to the model, the DAX measure commands may not be available. You can still use ordinary PivotTable summaries or create a new model-based PivotTable.

Prepare the Data Model first

Before writing a measure, make sure the source data is structured clearly. Convert a range to an Excel table with Insert > Table, give the table and its columns meaningful names, and check that numbers and dates have the correct data types. In this example, the model contains a table named Sales with columns Amount, Region, and Order ID. Its four rows have amounts of 20, 30, 40, and 40, so total sales are 130. The first two rows are North sales, and the other two are South sales; the data contains three distinct order IDs.

To create a PivotTable that uses the model, select a cell in the source table and choose Insert > PivotTable. In the dialog, select the option to add the data to the Data Model, then create the PivotTable. If you already have a model, you can also create a PivotTable from that workbook’s Data Model. For more complex reports, add related tables such as a calendar or product list and define relationships in the model; measures can then calculate across those relationships.

Power Pivot is the Excel feature used to manage the Data Model and define measures. In Windows desktop Excel, its ribbon tab may need to be enabled first: choose File > Options > Add-ins, select COM Add-ins in the Manage box, choose Go, and enable Microsoft Power Pivot for Excel if it is listed. Availability depends on the Excel edition. Excel for Mac and Excel for the web do not offer the same full Power Pivot model-authoring workflow, so create or edit DAX measures in supported Windows desktop Excel.

Create a DAX measure in Excel

With the model-based PivotTable active, use Power Pivot > Measures > New Measure. Depending on the Excel build and ribbon layout, you may also be able to right-click a table in the PivotTable Fields pane and choose Add Measure. In the New Measure dialog, choose the table that should own the measure, type a measure name, and enter the expression in the formula box. The measure name is entered separately from the formula.

Confirm that the active PivotTable uses the Data Model, not only a worksheet range.
Open the New Measure dialog and select the Sales table as the home table.
Name the measure Total Sales and enter the expression shown below.
Set a suitable number format, such as Currency or a number with two decimal places, and confirm the measure.
In the PivotTable Fields pane, expand Sales and drag Total Sales into Values. Add Region or another field to Rows to see the calculation split into groups.

Assuming the Data Model contains a Sales table with numeric values in its Amount column, this measure sums those values. The expression returns 130 for the complete example dataset, and it returns 50 for North when the PivotTable is filtered to North.

=SUM(Sales[Amount])

In DAX, the table name appears before the column name, and square brackets identify the column. If a table name contains spaces, enclose the table name in single quotation marks, as in 'Sales Data'[Amount]. A measure reference, by contrast, is written in square brackets, such as [Total Sales].

Useful measure examples

Measures become especially valuable when a calculation needs to respond to the PivotTable’s current grouping. For instance, the following measure counts unique order IDs rather than counting sales rows. It assumes the same Sales table, with three distinct values in its Order ID column, and returns 3 for the full dataset.

=DISTINCTCOUNT(Sales[Order ID])

You can combine measures to calculate an average order value. This is different from averaging the individual sales-line amounts: it divides total sales by the number of distinct orders. Assuming Sales has Amount and Order ID columns and the two measures above are already defined, the result is 130 divided by 3, or approximately 43.33.

=DIVIDE([Total Sales],[Order Count])

DIVIDE is generally preferable to using the division operator when the denominator could be zero. By default, DIVIDE returns a blank when the denominator is zero; it also supports an optional alternate result. Name the distinct-count measure Order Count before using the expression above, or replace that reference with the name you chose.

CALCULATE is another central DAX function. It evaluates an expression under specified filters. Assuming the Sales table has a Region column containing North and South, this measure returns North sales, which are 50 in the example. If a report already applies a filter to Region, the filter written inside CALCULATE normally replaces that filter for the same column.

=CALCULATE([Total Sales],Sales[Region]="North")

These examples use simple expressions, but the same pattern supports more involved calculations, including time comparisons and ratios. A time-intelligence calculation usually also needs a properly defined date table with continuous dates and a relationship to the sales table. The formula alone cannot compensate for missing or incorrectly related date data.

How filter context changes a result

To understand why a measure changes across a PivotTable, learn the basic idea of filter context. Each cell is evaluated using the filters that apply to it. A row labeled North filters the model to North; a month column filters it to that month; a slicer can add another condition. The Total Sales measure is the same in every cell, but SUM sees a different subset of rows in each context.

This is why a measure is not equivalent to copying a worksheet formula down a column. When you move Region from Rows to Filters, or select a product in a slicer, Excel recalculates the measure for the new context. Grand totals are also recalculated for the full context; they are not always the sum of the values displayed above them. For example, a distinct order count in a grand total counts unique orders across all visible regions, rather than adding regional counts that might include the same order in more than one region.

CALCULATE can modify that context, which is powerful but can surprise new users. If a formula must preserve an existing selection while adding a condition, the right DAX pattern depends on the intended behavior. Test measures with a small PivotTable containing the relevant fields, filters, and totals before relying on the result in a larger report.

Measure, calculated column, or PivotTable field?

A measure and a calculated column solve different problems. A measure is evaluated when the PivotTable requests a result and usually aggregates many rows. A calculated column is evaluated for each row in a model table and stores a value for that row. Use a measure for totals, averages, ratios, and other summaries; use a calculated column when you need a row-level value that can be used as a category, filter, or relationship key.

For example, if Sales has Quantity and Unit Price columns and you need each row’s line amount, a calculated column can multiply those fields. Assuming those two columns exist in the Sales table, the expression returns a separate result for each row: quantity 2 at a unit price of 10 produces 20. You could then sum the new column with a measure.

=Sales[Quantity]*Sales[Unit Price]

Do not confuse a DAX measure with a classic calculated field. A classic calculated field is a feature of a traditional PivotTable and has different capabilities and behavior; it is not a DAX measure in the Data Model. If a guide tells you to use PivotTable Analyze > Fields, Items, & Sets > Calculated Field, that is a different kind of calculation. Choose the model-based approach when you need DAX, relationships, or reusable measures.

Common problems and practical checks

The New Measure command is missing: Check that the PivotTable is based on the Data Model and that Power Pivot is available and enabled in your Excel edition. A standard range-based PivotTable does not automatically become a model PivotTable.
The formula reports an unknown name: Check the table and column names in the model, including spaces and punctuation. DAX names must match the model, not necessarily the original worksheet heading if it was renamed during import.
A total looks unexpected: Remember that a measure is recalculated in the total’s filter context. Distinct counts and ratios in particular should not be expected to add up like simple row values.
The result is blank: Check for empty data, filters that exclude all rows, or a zero denominator. For related-table calculations, confirm that the model relationships are active and connect the intended columns.
The number format is wrong: Set formatting on the measure in the model or New Measure dialog. A number that represents money should use a currency format; formatting does not change the underlying calculation.
The formula works in one report but not another: Inspect slicers, report filters, row fields, and relationships. The measure definition may be correct while the second PivotTable supplies a different context.

Frequently Asked Questions

Can I use a DAX formula in any Excel PivotTable?
No. DAX measures require a PivotTable connected to the Excel Data Model. A conventional PivotTable can use built-in summaries, but it does not support model measures in the same way.

Where do I type a DAX formula in Excel?
In Windows desktop Excel, open Power Pivot > Measures > New Measure while working with a model-based PivotTable. Enter the expression in the formula box and the measure name in its separate name field.

Should I use SUM or SUMX?
Use SUM when you are adding the values already stored in one numeric column. SUMX iterates over a table and evaluates an expression for each row before summing the results; it is useful for calculations that cannot be represented by a simple sum of an existing column.

Can I write DAX directly into a PivotTable cell?
No. Create the measure in the Data Model, then drag it into the PivotTable’s Values area. Typing a formula into a report cell would create a worksheet formula, not a DAX measure.

Can Excel for Mac create DAX measures?
Excel for Mac does not provide the full Windows Power Pivot authoring experience for creating and managing Data Model measures. Use supported Windows desktop Excel to author the model measure; availability and viewing behavior can vary by workbook and Excel version.

Does a DAX measure change the source data?
No. A measure defines a calculation in the Data Model and returns a result when a report requests it. It does not rewrite the worksheet’s source rows.

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.