Dynamic RANKX function

[Anonymous]

Hi,

I have a requirement where I have employees and their marks by class and year as mentioned below. In the below example, Class and Year are two slicers. Based on this slicers selection I have to rank the employees based on their marks.

Actual Data:

EmployeeName	Total Marks	Year	Class
Abhishek	79	2019	5
Anand	23	2019	5
Peter	90	2019	5
Bliss	34	2019	5
Mike	58	2018	6
Sofia	97	2018	6
Paul	47	2018	6
Chris	65	2018	6
Raman	45	2017	5
Raju	23	2017	5
Mohan	96	2016	4
Vinod	29	2016	4
Chaitanya	56	2016	4
Karthik	29	2016	4

For example 1: If I select 2019 as Year and 5 as Class, then my expected output should be

EmployeeName	Total Marks	Rank
Abhishek	79	2
Anand	23	4
Peter	90	1
Bliss	34	3

For example 2: If I select 2016 as Year and 4 as Class, then my expected output should be

EmployeeName	Total Marks	Rank
Mohan	96	1
Vinod	29	3
Chaitanya	56	2
Karthik	29	3

Can someone help me with how to get this in Power BI using DAX to work dynamically on Slicer selection?

Thanks in advance!

[Zubair_Muhammad · Accepted solution]

@Anonymous 

You can use following measure

Measure =
RANKX (
    ALLSELECTED ( Table1[EmployeeName] ),
    CALCULATE ( SUM ( Table1[Total Marks] ) ),
    ,
    DESC,
    DENSE
)

[Anonymous]

@Zubair_Muhammad  - Thanks for your help. This works!

[Anonymous]

Hi @Zubair_Muhammad 

I have another requirement along with the above requirement. 

The requirement is I already have Rank based on Total Marks. Now, I want the Row Number also to be displayed in the report. The key thing is it should be dynamic based on the year selection in the slicer. 

For Example: If I select 2018 a year, then the Rank and Row Number should start from 1 based on the Total Marks. The Rank column is working as per the requirement. Now I need the Row Number.

EmployeeName	Year	Total Marks	Rank	Row Number
Sofia	2019	97	1	1
Mohan	2019	96	2	2
Peter	2019	90	3	3
Abhishek	2019	90	3	4
Chris	2018	90	3	5
Mike	2018	58	4	6
Chaitanya	2018	58	4	7
Paul	2018	47	5	8
Raman	2017	45	6	9
Bliss	2017	34	7	10
Vinod	2017	34	7	11
Karthik	2017	29	8	12
Anand	2016	23	9	13
Raju	2016	23	9	14

Can you please help me with the same?

Thanks in advance