Forum Discussion
Help with Ranking and Quintile Calculation
I’m working on a mock schools test scores dashboard in Power BI, and I need help calculating:
The average score of the lowest and highest quintile
The ability for users to select a band of marks (20-25) and see the number and percentage of students in that range. My table looks like this
My Dataset (Results Table):
UPN – Unique ID for each student
Result – A number from 0 to 25 (test score) and they are a whole number
School Name – 10 different schools
I tried to rank students based on their test scores and assign them to quintiles:
Rank_Pupil = RANKX(ALL('MTC Results'), 'MTC Results'[Result], , ASC, DENSE)
And then tried to assign quintiles
Quintile =
VAR TotalStudents = COUNTROWS(ALL('MTC Results'))
VAR QuintileSize = ROUNDUP(TotalStudents / 5, 0)
VAR RankValue = RANKX(ALL('MTC Results'), 'MTC Results'[Result], , ASC, DENSE)RETURN
SWITCH(
TRUE(),
RankValue <= QuintileSize, "1st Quintile",
RankValue <= QuintileSize * 2, "2nd Quintile",
RankValue <= QuintileSize * 3, "3rd Quintile",
RankValue <= QuintileSize * 4, "4th Quintile",
"5th Quintile"
)- The quintile measure assigns "1st Quintile" to all students instead of distributing them properly.
- I can’t calculate the average of the lowest and highest quintiles properly.
- How do I let users select a score range (e.g., 20-25) and display the number and percentage of students in that range?Any help would be appreciated! Thanks.
Hi,
I am still unable to understand the desired result. If possible, can you share the download link of an MS Excel file. In that file, via Excel formulas, show the desired result. I will try to translate those formulas into measures.
11 Replies
Hi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
Hi Ashish_Mathur
This is a sample of what the data file looks like with about 500 rows of unique IDs:School name Gender Result student ID School 1 F 9 97284822 School 1 M 15 35221385 School 1 F 21 44096142 School 10 M 25 59477716 School 10 F 24 25130363 School 10 M 10 92790440 School 10 M 25 78879765 School 10 M 25 82761570 School 2 F 16 31826038 School 2 F 19 76562155 School 2 M 12 44052510 School 2 M 24 49889759 School 2 F 20 72768887 School 2 M 20 74528849 School 9 F 25 13687830 School 2 F 16 87886232 School 2 M 20 98243452 School 2 F 18 91741870 School 9 F 19 85182785 School 2 F 25 12870447 School 2 F 11 50403133 School 2 F 17 99132647 School 2 F 18 59752476 School 2 F 0 27568371 School 2 F 17 89879254 I am trying to show cards with lowest and highest average Quitile score overall (belowyou can see that still it doesnt show the highest quintile avg score even after applying the DAX mentioned by techies ) and also would be great if I can show all the quintiles 1,2,3,4,5 in a table with the average lowest and highest . It just assigns only Quintile 1 to the scores and I cant figure it out why . Thanks for your help
Hi,
I am still unable to understand the desired result. If possible, can you share the download link of an MS Excel file. In that file, via Excel formulas, show the desired result. I will try to translate those formulas into measures.
Hi Ashish_Mathur
This is a sample of what the data file looks like with about 500 rows of unique IDs:School name Gender Result student ID School 1 F 9 97284822 School 1 M 15 35221385 School 1 F 21 44096142 School 10 M 25 59477716 School 10 F 24 25130363 School 10 M 10 92790440 School 10 M 25 78879765 School 10 M 25 82761570 School 2 F 16 31826038 School 2 F 19 76562155 School 2 M 12 44052510 School 2 M 24 49889759 School 2 F 20 72768887 School 2 M 20 74528849 School 9 F 25 13687830 School 2 F 16 87886232 School 2 M 20 98243452 School 2 F 18 91741870 School 9 F 19 85182785 School 2 F 25 12870447 School 2 F 11 50403133 School 2 F 17 99132647 School 2 F 18 59752476 School 2 F 0 27568371 School 2 F 17 89879254 I am trying to show cards with lowest and highest average Quitile score overall (belowyou can see that still it doesnt show the highest quintile avg score even after applying the DAX mentioned by techies ) and also would be great if I can show all the quintiles 1,2,3,4,5 in a table with the average lowest and highest . It just assigns only Quintile 1 to the scores and I cant figure it out why . Thanks for your help
Anonymous , Try updating
dax
Quintile =
VAR TotalStudents = COUNTROWS(ALL('MTC Results'))
VAR QuintileSize = ROUNDUP(TotalStudents / 5, 0)
VAR RankValue = RANKX(ALL('MTC Results'), 'MTC Results'[Result], , ASC, DENSE)
RETURN
SWITCH(
TRUE(),
RankValue <= QuintileSize, "1st Quintile",
RankValue <= QuintileSize * 2, "2nd Quintile",
RankValue <= QuintileSize * 3, "3rd Quintile",
RankValue <= QuintileSize * 4, "4th Quintile",
"5th Quintile"
)dax
Average_Lowest_Quintile =
CALCULATE(
AVERAGE('MTC Results'[Result]),
'MTC Results'[Quintile] = "1st Quintile"
)Average_Highest_Quintile =
CALCULATE(
AVERAGE('MTC Results'[Result]),
'MTC Results'[Quintile] = "5th Quintile"
)Create a slicer for the score range:
Add a slicer to your Power BI report and set it to the Result column.
dax
Selected_Students_Count =
CALCULATE(
COUNTROWS('MTC Results'),
'MTC Results'[Result] >= MIN('MTC Results'[Result]),
'MTC Results'[Result] <= MAX('MTC Results'[Result])
)Total_Students = COUNTROWS('MTC Results')
Selected_Students_Percentage =
DIVIDE(
[Selected_Students_Count],
[Total_Students],
0
)Hi bhanu_gautam Thanks I tried to update the Quintile dax as you said but it gives me this message and I am not sure why . Any ideas much appreaciated .
Hi Anonymous to assign quintiles, create this calculated column
Quintile n =VAR TotalStudents = COUNTROWS(ALL('Sheet3'))VAR QuintileSize = INT(TotalStudents / 5)VAR RankValue = RANKX(ALL('Sheet3'), 'Sheet3'[Result], , ASC, DENSE)RETURNSWITCH(TRUE(),RankValue <= QuintileSize, "1st Quintile",RankValue <= QuintileSize * 2, "2nd Quintile",RankValue <= QuintileSize * 3, "3rd Quintile",RankValue <= QuintileSize * 4, "4th Quintile","5th Quintile")And measures as thisAvg_Highest_Quintile =CALCULATE(AVERAGE('Sheet3'[Result]),'Sheet3'[Quintile n] = "5th Quintile")Avg_Lowest_Quintile =CALCULATE(AVERAGE('Sheet3'[Result]),'Sheet3'[Quintile n] = "1st Quintile")For score range selection, create a parameterHi Anonymous,
May I ask if you have resolved this issue with the suggestions provided by techies ? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi Ashish_Mathur
This is a sample of what the data file looks like with about 500 rows of unique IDs:School name Gender Result student ID School 1 F 9 97284822 School 1 M 15 35221385 School 1 F 21 44096142 School 10 M 25 59477716 School 10 F 24 25130363 School 10 M 10 92790440 School 10 M 25 78879765 School 10 M 25 82761570 School 2 F 16 31826038 School 2 F 19 76562155 School 2 M 12 44052510 School 2 M 24 49889759 School 2 F 20 72768887 School 2 M 20 74528849 School 9 F 25 13687830 School 2 F 16 87886232 School 2 M 20 98243452 School 2 F 18 91741870 School 9 F 19 85182785 School 2 F 25 12870447 School 2 F 11 50403133 School 2 F 17 99132647 School 2 F 18 59752476 School 2 F 0 27568371 School 2 F 17 89879254 I am trying to show cards with lowest and highest in general and alkso would be great if I can show all the quintiles 1,2,3,4,5 in a table with the average lowest and highest perhaps . Thanks for your help
Hi Anonymous,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi Anonymous,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Thanks and regards
