Help with ranking Employees for each group using RANKX

[CamIAm88]

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

[v-jianboli-msft · Accepted 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.

[amitchandak]

@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
)

[CamIAm88]

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.