Forum Discussion
Using measure or Aggregation in AVERAGEX()
Hi,
I see different results when using either a measure or an aggregation as the expression to be evaluated in an AVERAGEX (or SUMX etc.).
With the following data:
Color Fruit Weight WeightType Green Apple 50 Dry Green Pear 60 Dry Red Strawberry 3 Dry Red Raspberry 1 Dry Red Pomegranate 50 Dry Green Apple 30 Wet Green Pear 60 Wet Red Strawberry 7 Wet Red Raspberry 4 Wet Red Granada 100 Wet
I'm running these measures:
AverageWeight_UsingSUM = AVERAGEX(VALUES(Table1[Fruit]), SUM(Table1[Weight])) AverageWeight_UsingMeasure = AVERAGEX(VALUES(Table1[Fruit]), [SumOfWeight]) SumOfWeight = SUM(Table1[Weight])
And get these results:
Now why exactly are the results different ? I guess probably because the context is evaluated differently, but I would have expected the same result...
Your example illustrates a couple of important points:
- When a measure is referred to within a DAX expression, its underlying code is automatically wrapped in CALCULATE
- Context transition: Within a row context, CALCULATE turns the row context into the equivalent filter context
Comparing your two measures:
AverageWeight_UsingSUM
AverageWeight_UsingSUM = AVERAGEX ( VALUES ( Table1[Fruit] ), SUM ( Table1[Weight] ) )
AverageWeight_UsingMeasure
AverageWeight_UsingMeasure = AVERAGEX ( VALUES ( Table1[Fruit] ), [SumOfWeight] )
is equivalent to
AverageWeight_UsingMeasure = AVERAGEX ( VALUES ( Table1[Fruit] ), CALCULATE ( SUM ( Table1[Weight] ) ) )
AverageWeight_UsingSUM doesn't produce a sensible result, because leaving out CALCULATE means the row context of the table VALUES ( Table1[Fruit] ) is never converted to filter context, so the same SUM is evaluated for each value of Fruit, then averaged.
For example, at a grand total level, since the total sum of Weight is 365 and there are 6 Fruit values,
AverageWeight_UsingSUM = (365 + 365 + 365 + 365 + 365 + 365)/6 = 365However AverageWeight_UsingMeasure produces a more sensible result because each row context of VALUES ( Table1[Fruit] ) is converted into a filter context (due to the implied CALCULATE) then the Weights are summed in the resulting filter context, then these sums are averaged.
For example, at a grand total level,
AverageWeight_UsingMeasure = (80 + 120 + 10 + 5 + 50 + 100)/6 = 60.83
Regards,
Owen
2 Replies
Your example illustrates a couple of important points:
- When a measure is referred to within a DAX expression, its underlying code is automatically wrapped in CALCULATE
- Context transition: Within a row context, CALCULATE turns the row context into the equivalent filter context
Comparing your two measures:
AverageWeight_UsingSUM
AverageWeight_UsingSUM = AVERAGEX ( VALUES ( Table1[Fruit] ), SUM ( Table1[Weight] ) )
AverageWeight_UsingMeasure
AverageWeight_UsingMeasure = AVERAGEX ( VALUES ( Table1[Fruit] ), [SumOfWeight] )
is equivalent to
AverageWeight_UsingMeasure = AVERAGEX ( VALUES ( Table1[Fruit] ), CALCULATE ( SUM ( Table1[Weight] ) ) )
AverageWeight_UsingSUM doesn't produce a sensible result, because leaving out CALCULATE means the row context of the table VALUES ( Table1[Fruit] ) is never converted to filter context, so the same SUM is evaluated for each value of Fruit, then averaged.
For example, at a grand total level, since the total sum of Weight is 365 and there are 6 Fruit values,
AverageWeight_UsingSUM = (365 + 365 + 365 + 365 + 365 + 365)/6 = 365However AverageWeight_UsingMeasure produces a more sensible result because each row context of VALUES ( Table1[Fruit] ) is converted into a filter context (due to the implied CALCULATE) then the Weights are summed in the resulting filter context, then these sums are averaged.
For example, at a grand total level,
AverageWeight_UsingMeasure = (80 + 120 + 10 + 5 + 50 + 100)/6 = 60.83
Regards,
Owen
Awesome..
Thank you !
