cancel
Showing results for
Did you mean:
Highlighted
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
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...

1 ACCEPTED SOLUTION

Accepted Solutions
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

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

2 REPLIES 2
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

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Frequent Visitor

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

Awesome..

Thank you !

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,742)