cancel
Showing results for
Did you mean:
Highlighted
TL5866 Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor

## Re: 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

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

Proud to be a Datanaut!

2 REPLIES 2
OwenAuger Super Contributor

## Re: 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

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

Proud to be a Datanaut!

TL5866 Frequent Visitor

## Re: Using measure or Aggregation in AVERAGEX()

Awesome..

Thank you !

Announcements   