cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TL5866 Frequent Visitor
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:

2018-06-21 13_03_32-Untitled - Power BI Desktop.png

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




View solution in original post

2 REPLIES 2
OwenAuger Super Contributor
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!




View solution in original post

TL5866 Frequent Visitor
Frequent Visitor

Re: Using measure or Aggregation in AVERAGEX()

Awesome..

Thank you !

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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