Forum Discussion
Working with percentiles
Hi all,
Hoping someone can help with the correct measure to calculate the which percentile a result falls into. Data set looks like this:
| Facility | Category | Result |
| Company A | Q1 | 71 |
| Company A | Q2 | 100 |
| Company A | Q3 | 94 |
| Company A | Q4 | 100 |
| Company A | Q5 | 100 |
| Company A | Q6 | 94 |
| Company A | Q7 | 94 |
| Company A | Q8 | 100 |
| Company A | Q9 | 100 |
| Company A | Q10 | 100 |
| Company A | Q11 | 100 |
| Company A | Q12 | 64 |
| Company B | Q1 | 87 |
| Company B | Q2 | 100 |
| Company B | Q3 | 93 |
| Company B | Q4 | 93 |
| Company B | Q5 | 93 |
| Company B | Q6 | 100 |
| Company B | Q7 | 93 |
| Company B | Q8 | 93 |
| Company B | Q9 | 100 |
| Company B | Q10 | 80 |
| Company B | Q11 | |
| Company B | Q12 | |
| Company C | Q1 | 94 |
| Company C | Q2 | 100 |
| Company C | Q3 | 94 |
| Company C | Q4 | 100 |
| Company C | Q5 | 100 |
| Company C | Q6 | 94 |
| Company C | Q7 | 100 |
| Company C | Q8 | 100 |
| Company C | Q9 | 94 |
| Company C | Q10 | 76 |
| Company C | Q11 | |
| Company C | Q12 | |
| Company D | Q1 | 86 |
| Company D | Q2 | 100 |
| Company D | Q3 | 100 |
| Company D | Q4 | 100 |
| Company D | Q5 | 100 |
| Company D | Q6 | 100 |
| Company D | Q7 | 100 |
| Company D | Q8 | 100 |
| Company D | Q9 | 100 |
| Company D | Q10 | 100 |
| Company D | Q11 | 100 |
| Company D | Q12 | 100 |
| Company E | Q1 | 87 |
| Company E | Q2 | 100 |
| Company E | Q3 | 100 |
| Company E | Q4 | 100 |
| Company E | Q5 | 100 |
| Company E | Q6 | 100 |
| Company E | Q7 | 100 |
| Company E | Q8 | 100 |
| Company E | Q9 | 100 |
| Company E | Q10 | 93 |
| Company E | Q11 | |
| Company E | Q12 | |
| Company F | Q1 | 67 |
| Company F | Q2 | 93 |
| Company F | Q3 | 93 |
| Company F | Q4 | 100 |
| Company F | Q5 | 80 |
| Company F | Q6 | 100 |
| Company F | Q7 | 100 |
| Company F | Q8 | 100 |
| Company F | Q9 | 100 |
| Company F | Q10 | 67 |
| Company F | Q11 | |
| Company F | Q12 |
There a lot more data in the set but this gives you the set up.
Companies have been asked a set of questions and results have been logged for each company against the questions. I'm trying to calculate which percentile of the results (per question) does Company A's answer fall into. i.e. Company A's result for Q1 was 71, what percentile does this fall into compared to all other Companies results for Q1?
Hoping that question makes sense. Thanks
Please try this measure expression in a matrix visual with Facility and Category to get the pic below.
Perc All =
VAR thisresult =
AVERAGE ( Perc[Result] )
VAR summary =
ADDCOLUMNS (
ALLSELECTED ( Perc[Category] ),
"@result",
CALCULATE (
AVERAGE ( Perc[Result] )
)
)
RETURN
COUNTROWS (
FILTER (
summary,
[@result] <= thisresult
)
)
/ COUNTROWS ( summary )Regards,
Pat
2 Replies
Please try this measure expression in a matrix visual with Facility and Category to get the pic below.
Perc All =
VAR thisresult =
AVERAGE ( Perc[Result] )
VAR summary =
ADDCOLUMNS (
ALLSELECTED ( Perc[Category] ),
"@result",
CALCULATE (
AVERAGE ( Perc[Result] )
)
)
RETURN
COUNTROWS (
FILTER (
summary,
[@result] <= thisresult
)
)
/ COUNTROWS ( summary )Regards,
Pat
Thank you, it took me a minute to get my head around all that, but it seems to have worked!
