cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dynamic RANKX function

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:

EmployeeNameTotal MarksYearClass
Abhishek7920195
Anand2320195
Peter9020195
Bliss3420195
Mike5820186
Sofia9720186
Paul4720186
Chris6520186
Raman4520175
Raju2320175
Mohan9620164
Vinod2920164
Chaitanya5620164
Karthik2920164

 

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

EmployeeNameTotal MarksRank
Abhishek792
Anand234
Peter901
Bliss343

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

EmployeeNameTotal MarksRank
Mohan961
Vinod293
Chaitanya562
Karthik293

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

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Dynamic RANKX function

@Anonymous 

 

You can use following measure

 

Measure =
RANKX (
    ALLSELECTED ( Table1[EmployeeName] ),
    CALCULATE ( SUM ( Table1[Total Marks] ) ),
    ,
    DESC,
    DENSE
)
Try my new Power BI game Cross the River

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: Dynamic RANKX function

@Anonymous 

 

You can use following measure

 

Measure =
RANKX (
    ALLSELECTED ( Table1[EmployeeName] ),
    CALCULATE ( SUM ( Table1[Total Marks] ) ),
    ,
    DESC,
    DENSE
)
Try my new Power BI game Cross the River

View solution in original post

Anonymous
Not applicable

Re: Dynamic RANKX function

@Zubair_Muhammad  - Thanks for your help. This works!

Anonymous
Not applicable

Re: Dynamic RANKX function

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.

EmployeeNameYearTotal MarksRankRow Number
Sofia20199711
Mohan20199622
Peter20199033
Abhishek20199034
Chris20189035
Mike20185846
Chaitanya20185847
Paul20184758
Raman20174569
Bliss201734710
Vinod201734711
Karthik201729812
Anand201623913
Raju201623914

 

Can you please help me with the same?

 

Thanks in advance

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors