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 performance against the average.

 

We need the below two higllighed colums :

 

 

 

 

 

 

Many thanks !

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]

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    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
    • Ankur's avatar
      Ankur
      Regular Visitor
      9 years ago

      Hello MFelix,

       

      We deployed the formula you suggested, below are the results we get :

       

       

       

       

       

       

       

      Thanks,

      Ankur

       

       

      • MFelix's avatar
        MFelix
        Super User
        9 years ago
        Is the average field on your table a calculation or a value in the data base?
  • 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.