Forum Discussion

Yes123's avatar
Yes123
Frequent Visitor
5 years ago
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 ...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    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] ) )
)
Yes123's avatar
Yes123
Frequent Visitor
5 years ago

I know it is slightly weird, but yes, that is how I need it.

AlexisOlson's avatar
AlexisOlson
Super User
5 years ago

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] ) )
)
  • Yes123's avatar
    Yes123
    Frequent Visitor
    5 years ago

    Awesome! Looks so simple, once you know. Thank you!

  • Yes123's avatar
    Yes123
    Frequent Visitor
    5 years ago

    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)

     

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      5 years ago

      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.