Need Group average at Row level in Table/Matrix

[Ankur]

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 !

[MFelix · Accepted solution]

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 · Accepted solution]

Can you try this:

Grp Avg =
CALCULATE (
    AVERAGEX ( VALUES ( Table[Salesman] ), [Amount] ),
    ALL ( Table[Salesman] )
)

Difference = [Amount] - [Grp Avg]

[Ankur]

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.

[MFelix · Accepted solution]

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]

Hello @MFelix,

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

Thanks,

Ankur

[MFelix]

Is the average field on your table a calculation or a value in the data base?

[Ankur]

Hi @MFelix,

It is a calculation field, based on Amount field.

[MFelix]

Can you share the formula?

MFelix