Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you, it took me a minute to get my head around all that, but it seems to have worked!