Forum Discussion
Help with ranking Employees for each group using RANKX
I have a dashboard created where the user selects an employee and a table displays metrics for all the work groups worked by that employee. I would like to add a column to the table on my dashboard that displays how that KPI ranks amongst all other users for that group. Please note, I would like to create a measure since my model is large and I am running low on space.
Below are examples of my existing data and the DAX I'm currently using to calculate the tranking:
DAX:
RankEmployee =
RANKX(
FILTER(
ALL(Table_1[Employee_A], (Table_1[Work_Group]),
Table_1[Work_Group] = MAX(Table_1[Work_Group])
),
CALCULATE( SUM( Table_1[KPI]) ) -- Note actual calculate simplified for example
)
Example of Existing Table (3 columns, Employee, Work_Group, KPI):
| Employee | Work_Group | KPI |
| A | Work Type A | 100 |
| A | Work Type B | 10 |
| A | Work Type C | 50 |
| B | Work Type A | 90 |
| B | Work Type B | 45 |
| B | Work Type C | 1 |
| C | Work Type A | 80 |
| C | Work Type B | 60 |
Example of the desired outcome for the table in the dashboard (4 columns, Employee, Work_Group, KPI, Rank):
| Employee | Work_Group | KPI | Rank |
| A | Work Type A | 100 | 1 |
| A | Work Type B | 10 | 3 |
| A | Work Type C | 50 | 1 |
| B | Work Type A | 90 | 2 |
| B | Work Type B | 45 | 2 |
| B | Work Type C | 1 | 2 |
| C | Work Type A | 80 | 3 |
| C | Work Type B | 60 | 1 |
Hi CamIAm88 ,
Please try:
RankEmployee = VAR _a = ADDCOLUMNS ( ALL ( Table_1 ), "Rank", RANKX ( FILTER ( ALL ( Table_1 ), [Work_Group] = EARLIER ( Table_1[Work_Group] ) ), [KPI] ) ) RETURN MAXX ( FILTER ( _a, [Employee] = MAX ( 'Table_1'[Employee] ) && [Work_Group] = MAX ( 'Table_1'[Work_Group] ) ), [Rank] )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
CamIAm88 , The measure you provided should work with small change
RankEmployee =
RANKX(
FILTER(
ALL(Table_1[Employee_A], (Table_1[Work_Group]),
Table_1[Work_Group] = MAX(Table_1[Work_Group])
)),
CALCULATE( SUM( Table_1[KPI]) ) -- Note actual calculate simplified for example
)Thank you for your quick response Amit. There was a typo in my DAX after rewriting it for the post but where I'm having the issue is with the results. The DAX designates a rank 1 to all work groups.
I would like for the measure to compare the KPI for the selected employee to that of all employees per Work_Group.
Hi CamIAm88 ,
Please try:
RankEmployee = VAR _a = ADDCOLUMNS ( ALL ( Table_1 ), "Rank", RANKX ( FILTER ( ALL ( Table_1 ), [Work_Group] = EARLIER ( Table_1[Work_Group] ) ), [KPI] ) ) RETURN MAXX ( FILTER ( _a, [Employee] = MAX ( 'Table_1'[Employee] ) && [Work_Group] = MAX ( 'Table_1'[Work_Group] ) ), [Rank] )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
