Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |