Microsoft Office Tutorials and References

In Depth Information

Choosing Between Calculated Columns and Measures

Now that you have seen the difference between calculated columns and measures, you

might be wondering when it is better to use calculated columns and when to use measures.

Sometimes, either is an option, but in most situations, your computation needs determine

your choice.

You have to define a calculated column (in the PowerPivot table grid window) whenever you

want to do the following:

■
Place the calculated results in an Excel Slicer or see results in Rows or Columns in a

pivot table (as opposed to the Values area).

■
Define an expression that is strictly bound to the current row. (For example, Price *

Quantity cannot work on an average of the two columns.)

■
Categorize text or numbers (for example, a range of values for a measure, a range of

ages of customers, and so on).

On the other hand, you have to define a measure (in the PowerPivot Field List in the pivot

table) whenever you want to display resulting calculation values that reflect pivot table

selections made by the user and see them in the Values area of pivot tables—for example:

■
When you calculate profit percentage of a pivot table selection

■
When you calculate ratios of a product compared to all products but filter both by year

or region

Some calculations can be covered both by calculated columns and measures, even if different

DAX expressions have to be used in these cases. For example, you can define the GrossMargin

as a calculated column:

= Sales[SalesAmount] - Sales[TotalProductCost]

but it can be defined as a measure too:

= SUM( Sales[SalesAmount] ) – SUM( Sales[TotalProductCost] )

The final result is exactly the same. We suggest you to favor the measure in this case because

it does not consume memory and disk space, but this is really important only in large

datasets. When the size of the workbook is not an issue, you can use the method you are more

comfortable with.