Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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.
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |