Using measure or Aggregation in AVERAGEX()

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