Jul 25, 2016

A Formula and a Measure are both calculations, but in general, you write a Formula to apply it to a PowerPivot Table whereas you write a Measure to apply to a PowerPivot PivotTable. In particular however the difference is in the way the calculations are performed…

When you write a Formula you write it to create a calculated column in a PowerPivot table, for example you might have a PowerPivot table that stores widths and lengths of all the rooms in a building amongst its other data, and you may now need a new column in that table that should store the areas of these rooms. In this occasion you need a calculated column using a Formula. Although the values of this column are calculated dynamically for each row of the table, they are less likely to change because the values of the width and length are pretty static. The Formula calculates area for every row irrespective of whether they will be used in a PivotTable or not. This way you are creating the data in the table before using it in the PivotTable.

On the other hand, imagine you have created a PivotTable out of the above table that shows you the total sum of the room-areas for each floor called RoomAreaPerFloor. If you now want to have a column in the PivotTable that calculates the percentages of each RoomAreaPerFloor as compared to the total FloorArea then you should create a Measure. Like a Formula, the Measure is also calculated on the fly when the PivotTable is being built but the Measure is calculated for each row of the PivotTable and not each row of the underlying Table. Therefore it may use some or all of the rows of the underlying table for its calculations depending on the query context of the PivotTable. These selected rows are usually the ones that are filtered in by the PivotTable therefore as the PivotTable’s query context changes the Measure calculates new values… and of course, the PivotTable’s query context is likely to change because that’s the whole idea of PivotTable. It’s there to extract different information by applying different queries on it.