Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
The Requirement is to show the "Top 3" and "Others" in the Pie Chart.
Following is the sample data:
Subject | Student | Marks |
Maths | John | 50 |
Maths | Sam | 60 |
Maths | Van | 70 |
Maths | Jim | 30 |
Maths | Jam | 20 |
Maths | Can | 90 |
English | John | 20 |
English | Sam | 30 |
English | Van | 40 |
English | Jim | 50 |
English | Jam | 60 |
English | Can | 70 |
French | John | 55 |
French | Sam | 75 |
French | Van | 15 |
French | Jim | 95 |
French | Jam | 80 |
French | Can | 20 |
Subject will be the filter.
need to obtain Top 3 Students based on their Marks. the ranking will change for every subject choosen from the filter.
I have tried using RANKX Function , but not able to get to the solution.
Any suggestions on this will be helpfull.
Thanks in advance
Solved! Go to Solution.
You can also use a MEASURE. This will allow you to determine RANK when you select more than 1 subject
RANK = RANKX ( ALLSELECTED ( TableName[Student ] ), CALCULATE ( SUM ( TableName[Marks] ) ), , DESC, DENSE )
How about adding a Calculated Column that will RANK students for each subject separately
RANK in each Subject = RANKX ( FILTER ( ALL ( TableName ), TableName[Subject] = EARLIER ( TableName[Subject] ) ), TableName[Marks], , DESC, DENSE )
HI @Cgowdar
Now you can also add another Column that will help in Pie Chart
Is Top 3 Student? = IF ( TableName[RANK in each Subject] <= 3, TableName[Student ], "Others" )
Thanks for the reply , this works perfectly fine.
but facing isssues while i have to apply other filers on the data
for example , currencly the filter is only on "Subject"
we may also have additional filters like "Month" or "Grade / Standard " etc
Kinldy share your views on this issue if possible.
Thanks in advance
HI @Cgowdar
I believe this MEASURE should work fine even with other FILTERs
To test it I added another column in your sample data "YEAR" and added it as a slicer
This measure is fine , but our main moto was to display the "Others" pie slice on the chart , where as this measure helps in displaying in the tool tip.
Thanks
OK, So you want to Group the "OTHERS" together which a MEASURE doesnot allow..Right?
Yes , Exactly
You can also use a MEASURE. This will allow you to determine RANK when you select more than 1 subject
RANK = RANKX ( ALLSELECTED ( TableName[Student ] ), CALCULATE ( SUM ( TableName[Marks] ) ), , DESC, DENSE )
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |