Power Pivot Excel Formulas: DAX Measures and Columns

Charts & pivot tables · Updated November 21, 2026

Power Pivot formulas are written in Data Analysis Expressions (DAX), not with ordinary Excel worksheet formulas. Use a DAX measure for results that should recalculate with a PivotTable’s filters, and a calculated column for a value stored on each row of a table.

What Power Pivot formulas mean

Power Pivot is Excel’s data-modeling feature. It lets you load tables into a workbook Data Model, connect related tables, and summarize data with PivotTables. DAX is the formula language used to create calculations in that model. It resembles Excel formulas in some ways, but it has its own rules for table references, row and filter context, and calculation behavior.

A common point of confusion is the difference between Power Pivot and Power Query. Power Query uses the M language to import and transform data. Power Pivot uses DAX to calculate results from data that is already in the Data Model. A worksheet formula such as a cell reference calculation is not automatically a DAX formula; DAX calculations belong to model tables or measures.

For examples below, imagine a Sales table with three records: product 10 has quantity 2 and amount 100; product 20 has quantity 1 and amount 80; product 10 has quantity 3 and amount 150. A related Product table identifies product 10 as Bikes and product 20 as Helmets. The total sales amount is 330, total quantity is 6, and Bikes sales total 250.

Measures versus calculated columns

A measure calculates an aggregate when a report or PivotTable requests it. Its result can change as the user filters dates, products, or regions. Measures are generally the right choice for totals, averages, ratios, counts, and other report summaries. In the New Measure dialog, enter the formula in the formula box and give the measure a name in the name field.

With a Sales table containing an Amount column with values 100, 80, and 150, use this measure formula; it returns 330 when no filters are applied:

=SUM(Sales[Amount])

A calculated column works differently: it evaluates for each row and stores a result in the model. With Quantity and Unit Price columns in the Sales table, this calculated-column formula returns 100, 80, and 150 for rows whose quantity and unit prices are 2 and 50, 1 and 80, and 3 and 50 respectively:

=[Quantity]*[Unit Price]

Calculated columns can be useful for categories or row-level values that need to act as PivotTable fields. However, they consume model storage and usually do not adapt to report filters. If a value is only needed as a summarized result, prefer a measure. Do not create a row-by-row column just to calculate a grand total.

Creating a basic measure

In Excel for Microsoft 365 on Windows, load compatible tables into the Data Model, then open Power Pivot > Manage. In the Power Pivot window, select the table where you want the measure stored and choose Home > AutoSum for a quick sum, or use the measure command to define one yourself. In the Excel window, the Power Pivot tab and its commands may need to be enabled through Excel add-ins if the tab is not visible.

For a measure named Total Sales, the formula box assumes the Sales table has an Amount column containing 100, 80, and 150; the result without filters is 330:

=SUM(Sales[Amount])

Use the measure in a PivotTable by dragging it into Values. Put fields such as Product Category or Year in Rows, Columns, or Filters to break down its result. A measure is not a cell formula copied down a worksheet. Excel recalculates it for each combination of fields and filters in the PivotTable.

On Mac, Power Pivot authoring and management capabilities are not the same as on Windows, and the full Power Pivot window is not generally available. Excel for the web also does not provide the full Power Pivot modeling interface. A workbook with an existing Data Model may offer some PivotTable interaction in those environments, but use desktop Excel for Windows to create or edit Power Pivot models and DAX measures.

Useful DAX formula patterns

Most starting calculations use familiar aggregation functions. If the Sales table has Amount values 100, 80, and 150, this measure returns 330:

=SUM(Sales[Amount])

If the Sales table has ProductID values 10, 20, and 10, this measure counts two different products, not three sales rows:

=DISTINCTCOUNT(Sales[ProductID])

To calculate a ratio safely, use DIVIDE rather than the division operator alone. With measures named Total Sales and Total Units, based on Amount values totaling 330 and Quantity values totaling 6, this returns 55:

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

DIVIDE handles a zero denominator by returning a blank by default, rather than producing a division error. You can optionally specify a different alternate result as its third argument.

CALCULATE evaluates an expression under a specified filter. With a Product table containing ProductID 10 for Bikes and a relationship from Product to Sales, this measure returns 250 because the two Bikes sales rows total 100 plus 150:

=CALCULATE([Total Sales],Product[ProductID]=10)

These examples rely on the measure name Total Sales already existing and on the stated table columns being present. In practice, create a small set of reusable base measures first, then build ratios or filtered measures from them. That makes a model easier to maintain than repeating complicated expressions throughout a workbook.

Relationships and filter context

Relationships let a field in one table filter calculations over another. In the example model, Product[ProductID] must connect to Sales[ProductID]. With that relationship in place, putting Product[Category] in PivotTable rows can divide the Total Sales measure into Bikes and Helmets. The values are 250 and 80, and the grand total remains 330.

Without the relationship, the category field cannot correctly filter the Sales records. Check that key columns use compatible data types, that the lookup side contains unique keys, and that the relationship is active. A common model design uses a one-to-many relationship from a distinct product list to many sales rows.

Filter context means the set of filters active when a measure is evaluated: for example, a selected year, product category, or region. Row context is the current row used when DAX calculates a calculated column or iterates over rows. These concepts explain why a measure can return different values in separate PivotTable rows while a calculated column generally holds the same stored value until the model is refreshed.

Entering and checking formulas

When creating a measure, choose a clear name, enter the DAX expression, and select a number format suited to the result, such as currency for sales or a percentage for a rate. In the Power Pivot window, formulas can be entered in the calculation area or through the measure dialog. If Excel reports an error, check the table and column names, brackets, commas, and whether the formula is being entered as a measure or a calculated column.

A practical way to validate a new calculation is to test it against a tiny, manually checkable dataset. For the sample Sales records, the sum is 100 + 80 + 150 = 330, while quantity is 2 + 1 + 3 = 6. If the PivotTable does not show those totals, inspect active filters and relationships before assuming the arithmetic is wrong.

  • Confirm that the source tables have been added to the Data Model, rather than only loaded to worksheet cells.
  • Verify table and column names in the formula editor; DAX references use the model’s names.
  • Check whether the calculation should be a measure or a calculated column.
  • Review filters and relationships when a correct-looking measure gives an unexpected total.
  • Set the measure’s number format in the model so values display consistently in PivotTables.

Common mistakes and limits

One frequent mistake is expecting a measure to appear as a value on every source row. Measures summarize dynamically; calculated columns provide row-level results. Another is assuming a measure will ignore a PivotTable filter. Most measures respond to the current filter context unless the DAX expression specifically changes that context.

Also, a calculated column is not a substitute for fixing source data. If an imported date or amount is malformed, clean or correct the underlying data rather than relying on a formula to conceal every issue. Large models can use substantial memory, and unnecessary calculated columns increase the model’s size. Keep the model focused on fields and calculations that reports actually need.

Power Pivot is not available with the same feature set in every Excel edition or platform. The Windows desktop application is the standard environment for creating and editing a Power Pivot model. If a workbook is shared with people using Mac or the web version, check that their required tasks are supported and that the model and PivotTables remain usable in their version.

Frequently Asked Questions

Are Power Pivot formulas the same as Excel formulas?
No. Power Pivot calculations use DAX and refer to model tables and columns. They can look familiar, but DAX has distinct behavior for measures, calculated columns, relationships, and filter context.

Should I use a measure or a calculated column?
Use a measure for a result that should aggregate and respond to PivotTable filters, such as total sales. Use a calculated column when each record needs its own stored value or category that will be used as a field.

Can I use worksheet functions in Power Pivot?
Not all worksheet functions are available in DAX, and similar functions may behave differently. Check whether a function exists in DAX and use DAX syntax for model calculations; worksheet formulas remain in worksheet cells.

Why does my measure show the same result for every category?
Check that the category table is related to the table being summarized, and that the relationship is active. Also review the measure for logic that removes or overrides the category filter.

Does Power Pivot use DAX or Power Query M?
Power Pivot uses DAX for model calculations. Power Query uses M to connect to, clean, and transform data before loading it. A typical workflow may use both, but they serve different purposes.

Choosing a good starting point

For a first Power Pivot model, start with a clean fact table such as Sales and small lookup tables such as Product or Calendar. Create relationships using reliable keys, then define a few core measures for totals and counts. Build a PivotTable to check the results by category or date before adding more complex logic.

The central distinction is simple: measures answer questions about the currently selected data, while calculated columns add a value to each row. Understanding that difference, along with relationships and filter context, makes DAX formulas easier to choose, test, and explain.

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.