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.
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!
Solved! Go to Solution.
@Anonymous
You can use following measure
Measure = RANKX ( ALLSELECTED ( Table1[EmployeeName] ), CALCULATE ( SUM ( Table1[Total Marks] ) ), , DESC, DENSE )
@Anonymous
You can use following measure
Measure = RANKX ( ALLSELECTED ( Table1[EmployeeName] ), CALCULATE ( SUM ( Table1[Total Marks] ) ), , DESC, DENSE )
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
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |