Forum Discussion
Solved
Help with Average per team distribution
Okay, so I have searched for a solution but have not been able to solve it. I guess I should use AverageX, but how is the question? Might just be easier to look at the picture below. So I have a ...
You can aggregate subtotal averages rather than a standard average like this:
AVERAGEX ( VALUES ( Sales[Team] ), [ProductAvg] )where [ProductAvg] is whatever measure you already have defined, presumably something like
DIVIDE ( SUM ( Sales[Amount] ), CALCULATE ( SUM ( Sales[Amount] ), ALLSELECTED ( Sales[Product] ) ) )
You can aggregate subtotal averages rather than a standard average like this:
AVERAGEX ( VALUES ( Sales[Team] ), [ProductAvg] )where [ProductAvg] is whatever measure you already have defined, presumably something like
DIVIDE (
SUM ( Sales[Amount] ),
CALCULATE ( SUM ( Sales[Amount] ), ALLSELECTED ( Sales[Product] ) )
)
One question though and I'm sure this a stupid one... It works fine, but why can't I have the [ProductAvg] as a VAR? Doing it that way it is slightly of
Measure =
VAR Per_Team = DIVIDE (
SUM ( Sales[Amount] ),
CALCULATE ( SUM ( Sales[Amount] ), ALLSELECTED ( Sales[Product] ) )
)
Return AVERAGEX(VALUES(Sales[Team]),Per_Team)
When you declare a variable, it is computed once and reused as a constant through the rest of the measure, so you'd be averaging the same value ignoring the Team.