Working with percentiles

[EBurke32]

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

[mahoneypat · Accepted 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

[EBurke32]

Thank you, it took me a minute to get my head around all that, but it seems to have worked!