Using measure or Aggregation in AVERAGEX()

[TL5866]

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...

[OwenAuger · Accepted solution]

@TL5866

Your example illustrates a couple of important points:

1. When a measure is referred to within a DAX expression, its underlying code is automatically wrapped in CALCULATE
2. 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 = 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

[TL5866]

Awesome..

Thank you !