Forum Discussion

EBurke32's avatar
EBurke32
New Member
5 years ago
Solved

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 Comp...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

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's avatar
    EBurke32
    New Member
    5 years ago

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