Forum Discussion

Ankur's avatar
Ankur
Regular Visitor
9 years ago
Solved

Need Group average at Row level in Table/Matrix

Hi,   We are building a sales comparision report across the terrority by salesperson. We need the group average sales to be availalbe at each row, so what we can compare individual salesperson perf...
Need Help
  • MFelix's avatar
    MFelix
    9 years ago
    Hi Ankur
    Add this two measures two your table:

    Average_Total = CALCULATE(AVERAGE(Sales[Amount]),ALLSELECTED(Sales))

    Average difference = SUM(Sales[Amount])-[Average_Total]
    Then add them to your visual.

    Regards,

    MFelix
  • mattbrice's avatar
    mattbrice
    9 years ago

    Can you try this:

     

    Grp Avg =
CALCULATE (
    AVERAGEX ( VALUES ( Table[Salesman] ), [Amount] ),
    ALL ( Table[Salesman] )
)
    Difference = [Amount] - [Grp Avg]
mattbrice's avatar
mattbrice
Solution Sage
9 years ago

Can you try this:

 

Grp Avg =
CALCULATE (
    AVERAGEX ( VALUES ( Table[Salesman] ), [Amount] ),
    ALL ( Table[Salesman] )
)
Difference = [Amount] - [Grp Avg]
Ankur's avatar
Ankur
Regular Visitor
9 years ago

Hi MFelix , mattbrice,

 

Did some small change and it worked :

 

Group_Average = CALCULATE(SUM(Sales[Amount]),ALLSELECTED(Sales)) / 8

 

Divided the result by the no. of salesman. Now the result is what we wanted.

 

 

 

 

Many Thanks.