Replace Calculated Fields with DAX

March 24, 2023 - by Bill Jelen

The DAX formula language really shines when you use it to create a new measure for your pivot table. DAX measures are in the same genre as calculated fields, but are infinitely more powerful.

To create a new measure, right-click the table name in the Pivot Table Fields pane and choose Add Measure.

In the Pivot Table Fields pane, right-click any table name and choose Add Measure. — Figure 1062. Define a new measure.

Build the measure in the Measure Settings dialog. Use the Check Formula button to check the syntax.

In the Measure Settings dialog, the Measure Name is Avg Bonus Rate. The formula is =sum(Sales[Bonus]/sum(Sales[Revenue]) — Figure 1063. Define a new measure.

Excel will calculate the measure once for every value cell in the pivot table. In the figure below, this means that the calculation will happen 39 times. This is faster than adding the calculation to a million rows of source data.

Measures only get calculated once per cell in the finished pivot table. Even if the underlying data is millions of records, you are doing relatively few calculations. On this case, 39 cells in the Values area of the pivot table, including Grand Totals. — Figure 1064. Excel calculates the formula 39 times in this pivot table.

A complete DAX reference can be found online, and I won’t attempt to replicate that guide here. However, the following topics are the a-ha moments in my power pivot learning curve.

This article is an excerpt from Power Excel With MrExcel

Title photo by Greg Rosenke on Unsplash