Answered by:
Measures

(Sorry for the question...)
Can anybody explain what is the big deal about the measures?
Why is it better then a calculated column?
the only diference I foid between the 2 is in aggregate functions (so if I drag it to the values I get sum of sums)
but in that case I only have to drag the revenues to the values in the pivot table, and don't need to calculate on the data anyway...
Thanks
Question
Answers

The main difference between calculated columns and measures is that calculated columns are a fixed property of a row. That is to say, once the calculated column is calculated, it will retain the same value until the data is refreshed (and new data affects the result) or if you change/update the formula. Measures on the other hand are dynamically calculated and can be affected by the user's selection.
Since calculated columns are persisted in the model and treated as though they were any other column once they have been calculated, they will add to the total size of the Power Pivot model. Measures, on the other hand, do not add to the total size of model because they are only calculated in memory when they are used (directly or indirectly).
Another important difference is that you can use calculated columns on Rows, Columns, Filters, and Slicers in a Pivot Table or Chart because they are treated no differently from native columns. However, measures can only be used in the Values area in Pivot Table and Charts.
With regards to the benefit of explicit measures (i.e. measures that are predefined) vs implicit measures (i.e. one created from drag a column into the Values area), one issue is that implicitly defined measures are restricted in terms of their complexity. If you require a calculation that goes beyond simple aggregations, you will need to explicitly define the measure in the model where you can manually handle the calculation logic. An example of such a measure could be a measure that calculates a value for the same period in the previous year. Another benefit of explicitly defined measures is that they can prevent your end users from applying the wrong aggregation to a column. When a user drags the column into the values area, they could very easily get the wrong aggregation type for a 'Price' column for example; they could get a SUM instead of an AVERAGE, MIN, or MAX which would give the wrong result.
The explanations I have given above are somewhat simplified but will give you a general idea of the differences.
To get a more indepth understanding of the difference, I would recommend reading the following articles...
Context in DAX Formulas
http://technet.microsoft.com/enus/library/gg413423.aspx
Calculated Fields in Power Pivot
http://office.microsoft.com/engb/excelhelp/calculatedfieldsinpowerpivotHA102837191.aspx
When to Use Measures vs. Calc Columns
http://www.powerpivotpro.com/2013/02/whentousemeasuresvscalccolumns
Create a Measure in a PivotTable or PivotChart
http://technet.microsoft.com/enus/library/gg399161.aspx
Regards,
Michael
Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)
 Edited by Michael AmadiModerator Saturday, February 22, 2014 7:56 AM Added more detail
 Proposed as answer by Michael AmadiModerator Saturday, February 22, 2014 11:20 PM
 Marked as answer by Mike DietterickEditor Thursday, February 27, 2014 2:29 PM
All replies

The main difference between calculated columns and measures is that calculated columns are a fixed property of a row. That is to say, once the calculated column is calculated, it will retain the same value until the data is refreshed (and new data affects the result) or if you change/update the formula. Measures on the other hand are dynamically calculated and can be affected by the user's selection.
Since calculated columns are persisted in the model and treated as though they were any other column once they have been calculated, they will add to the total size of the Power Pivot model. Measures, on the other hand, do not add to the total size of model because they are only calculated in memory when they are used (directly or indirectly).
Another important difference is that you can use calculated columns on Rows, Columns, Filters, and Slicers in a Pivot Table or Chart because they are treated no differently from native columns. However, measures can only be used in the Values area in Pivot Table and Charts.
With regards to the benefit of explicit measures (i.e. measures that are predefined) vs implicit measures (i.e. one created from drag a column into the Values area), one issue is that implicitly defined measures are restricted in terms of their complexity. If you require a calculation that goes beyond simple aggregations, you will need to explicitly define the measure in the model where you can manually handle the calculation logic. An example of such a measure could be a measure that calculates a value for the same period in the previous year. Another benefit of explicitly defined measures is that they can prevent your end users from applying the wrong aggregation to a column. When a user drags the column into the values area, they could very easily get the wrong aggregation type for a 'Price' column for example; they could get a SUM instead of an AVERAGE, MIN, or MAX which would give the wrong result.
The explanations I have given above are somewhat simplified but will give you a general idea of the differences.
To get a more indepth understanding of the difference, I would recommend reading the following articles...
Context in DAX Formulas
http://technet.microsoft.com/enus/library/gg413423.aspx
Calculated Fields in Power Pivot
http://office.microsoft.com/engb/excelhelp/calculatedfieldsinpowerpivotHA102837191.aspx
When to Use Measures vs. Calc Columns
http://www.powerpivotpro.com/2013/02/whentousemeasuresvscalccolumns
Create a Measure in a PivotTable or PivotChart
http://technet.microsoft.com/enus/library/gg399161.aspx
Regards,
Michael
Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)
 Edited by Michael AmadiModerator Saturday, February 22, 2014 7:56 AM Added more detail
 Proposed as answer by Michael AmadiModerator Saturday, February 22, 2014 11:20 PM
 Marked as answer by Mike DietterickEditor Thursday, February 27, 2014 2:29 PM
