none
Measures

    Question

  • (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

    Saturday, February 22, 2014 3:29 AM

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 pre-defined) 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 in-depth understanding of the difference, I would recommend reading the following articles...

    Context in DAX Formulas

    http://technet.microsoft.com/en-us/library/gg413423.aspx

    Calculated Fields in Power Pivot

    http://office.microsoft.com/en-gb/excel-help/calculated-fields-in-power-pivot-HA102837191.aspx

    When to Use Measures vs. Calc Columns

    http://www.powerpivotpro.com/2013/02/when-to-use-measures-vs-calc-columns

    Create a Measure in a PivotTable or PivotChart

    http://technet.microsoft.com/en-us/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 :)

    Saturday, February 22, 2014 7:39 AM

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 pre-defined) 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 in-depth understanding of the difference, I would recommend reading the following articles...

    Context in DAX Formulas

    http://technet.microsoft.com/en-us/library/gg413423.aspx

    Calculated Fields in Power Pivot

    http://office.microsoft.com/en-gb/excel-help/calculated-fields-in-power-pivot-HA102837191.aspx

    When to Use Measures vs. Calc Columns

    http://www.powerpivotpro.com/2013/02/when-to-use-measures-vs-calc-columns

    Create a Measure in a PivotTable or PivotChart

    http://technet.microsoft.com/en-us/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 :)

    Saturday, February 22, 2014 7:39 AM
  • WOW!

    Thank you so much!

    Where can I vote it as helpful?

    Saturday, February 22, 2014 7:10 PM