Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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...
Solved! Go to Solution.
Your example illustrates a couple of important points:
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 = 365
However 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
Your example illustrates a couple of important points:
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 = 365
However 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 !
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |