Help with Ranking and Quintile Calculation

[linabramley]

Hello everyone,

I’m  working on a mock schools test scores dashboard in Power BI, and I need help calculating:

1. The average score of the lowest and highest quintile 

2. 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.

[v-nmadadi-msft]

Hi @linabramley,

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

[v-nmadadi-msft]

Hi @linabramley,

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.

[linabramley]

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

[Ashish_Mathur]

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.

[linabramley]

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

[linabramley]

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

[Ashish_Mathur · Accepted solution]

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.

[linabramley]

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 . 

[techies]

Hi @linabramley 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)

RETURN

    SWITCH(

        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 this

Avg_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 parameter

[v-nmadadi-msft]

Hi @linabramley,

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.

[bhanu_gautam]

@linabramley , 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
)