Forum Discussion
Help with Ranking and Quintile Calculation
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.
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
)