Forum Discussion
Help with Ranking and Quintile Calculation
Hello everyone, 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 u...
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 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)
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
Hi 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.
