Ranking employees with managers in table

[ClemTiger0408]

I am trying to rank all employees by NPS (which is calculated as the average of NPS by row) in a table with the manager alongside the employee name but not included in the ranking group.

From my research I tried the formula below:

RANKX(ALLSELECTED (Table1), calculate(average(Table1[SPS])),,desc,dense) 

But when I try it, everyone ties at 2. I think this is because the data has every single NPS survey result (meaning many for each employee) so the ALLSELECTED is every single line (cold be wrong).

If I try:

RANKX(ALLSELECTED (Table1[employee]),calculate(average(Table1[SPS])),,desc,dense)

then the result is ranking each employee within the manager grouping as opposed to ranking all the employees amongst each other.

I'm not quite sure what I'm doing wrong. Thanks ahead of time!

[v-juanli-msft]

Hi @ClemTiger0408 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @ClemTiger0408 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @ClemTiger0408 

If i understand you correctly, you could create measures as below :

avg = CALCULATE(AVERAGE('Table'[NPS]),ALLEXCEPT('Table','Table'[employee]))

rank regradless of manager = RANKX(ALL('Table'),[avg],,DESC,Dense)

avg2 = AVERAGE([NPS])

rank by manager group = RANKX(ALL('Table'[employee]),[avg2],,DESC,Dense)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.