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.
Search JabSto ::




Custom Search