Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
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
OwenAuger
Super User
Super User

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Awesome..

Thank you !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.