Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EBurke32
New Member

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:

FacilityCategoryResult
Company AQ171
Company AQ2100
Company AQ394
Company AQ4100
Company AQ5100
Company AQ694
Company AQ794
Company AQ8100
Company AQ9100
Company AQ10100
Company AQ11100
Company AQ1264
Company BQ187
Company BQ2100
Company BQ393
Company BQ493
Company BQ593
Company BQ6100
Company BQ793
Company BQ893
Company BQ9100
Company BQ1080
Company BQ11 
Company BQ12 
Company CQ194
Company CQ2100
Company CQ394
Company CQ4100
Company CQ5100
Company CQ694
Company CQ7100
Company CQ8100
Company CQ994
Company CQ1076
Company CQ11 
Company CQ12 
Company DQ186
Company DQ2100
Company DQ3100
Company DQ4100
Company DQ5100
Company DQ6100
Company DQ7100
Company DQ8100
Company DQ9100
Company DQ10100
Company DQ11100
Company DQ12100
Company EQ187
Company EQ2100
Company EQ3100
Company EQ4100
Company EQ5100
Company EQ6100
Company EQ7100
Company EQ8100
Company EQ9100
Company EQ1093
Company EQ11 
Company EQ12 
Company FQ167
Company FQ293
Company FQ393
Company FQ4100
Company FQ580
Company FQ6100
Company FQ7100
Company FQ8100
Company FQ9100
Company FQ1067
Company FQ11 
Company FQ12 

 

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

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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 )

 

mahoneypat_0-1605236641533.png

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Employee
Employee

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 )

 

mahoneypat_0-1605236641533.png

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors