Power Pivot Principles: Calculated Field vs. Measure

Welcome back to the Power Pivot Principles blog. This week, we consider the differences between creating a calculated field and a calculated measure.

Let’s revisit our dataset from last week:

Similar to last week, I want to calculate Gross Profit.  Last week, we did this by creating two measures:

Alternatively, I could just create a calculated field in the source Table, viz.

Here, I have just added a column and subtracted Costs from Sales. Simple.

But which is the better way?

The difference between the two represents the context of evaluation. A measure is evaluated in aggregate in the context of the cell evaluated in a report, PivotTable or DAX query, whereas a calculated column is computed at the row level within the table it belongs to.

In general, measures are used to calculate aggregates, such as the sum or average of a column. Moreover, measures are calculated at the time the query is displayed, which means that they aren’t stored in your database. They are simply executed and processed at the time of your request (e.g. when you place them in a PivotTable). Since measures are not stored in memory, they are generally faster, although it is important to recognise the trade-off between using in-memory storage or processing power in an instance where either a measure or a calculated field (column) could be used.

As usual, it depends, but it is a numbers game. If my dataset contained millions of rows, the measure would win hands down.

That’s it for this week!


Come back next week to see how we use the disconnected table for performing a sensitivity analysis by staying tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.