Getting a list of values above the 90th percentile

[PA7177]

I want to get a list of clients who's $ amount are greater than or equal to the 90th percentile of all clients $ amount. How can I put this into DAX? 

[josh84kf]

If found this solution to this problem:

percentile =
VAR t =
SUMMARIZE(ALLSELECTED('Table'),
'Table'[ID],
"Total", CALCULATE(SUM('Table'[Value]))
)
RETURN PERCENTILEX.INC(t, [Total], 0.9)

[amitchandak]

Refer

https://community.powerbi.com/t5/Desktop/Calculating-the-percentile-for-a-set-of-data/td-p/250581

[PA7177]

Hi Amit,

I have already used this formula. It identifies the 90th percentile but does not give me a list of clients in the 90th percentile. It just repeats the value because it is calculating the 90th percentile for each row.

See below:

I want to know which clients these are in a tabular form

[amitchandak]

Refer:https://community.powerbi.com/t5/Desktop/Calculate-90th-percentile-with-date-filter/td-p/641198

https://blog.pragmaticworks.com/replicating-excel-percentile-in-dax

[PA7177]

Amit,

The first link you posted is the same solution you provided earlier. When using the formula, it does not give me the 90th percentile of the data, it gives me the 90th percentile of each row.

The second link is not addressing my issue.

Thank you

[v-lid-msft · Accepted solution]

Hi @PA7177 ,

We can try to apply a measure in visual filter as following to meet your requirement:

90th percentile = PERCENTILEX.EXC(SUMMARIZE('Table','Table'[ID]),CALCULATE(SUM('Table'[$])),0.9)

Visual Control = IF(SUM('Table'[$]) <CALCULATE([90th percentile],ALLSELECTED('Table')),-1,1)

[90th percentile] in Visual Control Measure can be replaced with your measure.

Best regards,

[jorpuentes]

Thank you!!!!

[PA7177]

One issue I found is the last number prior to the 90th percentile cut off is not identified as within the 90th percentile. Any idea why?

[v-lid-msft]

Hi @PA7177 ,

Could you please share the formula of your 90th Percentile measure? What is the result if put the following test measure into table visual, does it will show 401887?

Test Measure = CALCULATE([90th percentile],ALLSELECTED('Table'))

Best regards,

[PA7177]

Hi,

I used the measures you provided and just replaced the column and table names.

In another example, when I tried the Test Measure you provided here, I get $411,200 as the 90th Percentile but when I have the scatterplot and use the 90th percentile from the analytics feature, it gives me $398,856.

I do not have any different filters between the visuals so I can rule that out.

[v-lid-msft]

Hi @PA7177 ,

We can use the following measure to fix it:

90th percentile = 
var t = SUMMARIZE('Table','Table'[ID],"Total",CALCULATE(SUM('Table'[$])))
return MINX(FILTER(ADDCOLUMNS(t,"Rank",RANKX(t,[Total],,ASC)),[Rank]>=ROUNDUP(COUNTROWS(t) * 0.9,0)),[Total])

Best regards,

[PA7177]

It still gives me the wrong value.

Is this measure taking into account report-level filters?

My report-level filters include cost bands, IDs, and service types.

Thanks

[v-lid-msft]

Hi @PA7177 ,

We think if the result will change with your change of report-level filter, the report-level filter should not be the reason of incorrect result. Does the new measure still have the same result as the earlier measure?

90th percentile =
VAR t =
    SUMMARIZE ( 'Table', 'Table'[ID], "Total", CALCULATE ( SUM ( 'Table'[$] ) ) )
RETURN
    MINX (
        FILTER (
            ADDCOLUMNS ( t, "Rank", RANKX ( t, [Total],, ASC ) ),
            [Rank]
                >= ROUNDUP ( COUNTROWS ( t ) * 0.9, 0 )
        ),
        [Total]
    )

We will try to explain the above measure to make it understandable, 

1. var t is a summarize table, it will get all the ID in context, then add a total column associate with it, total column is the sum of sales.

2. Then the ADDCOLUMNS ( t, "Rank", RANKX ( t, [Total],, ASC ) ) add rank column in to t table, it will rank with total column in ASC order

3. ROUNDUP ( COUNTROWS ( t ) * 0.9, 0 ) is used to get the rank value of 90%, if the rank column is coming from 1 to 11, the result will be 11*0.9=9.9, then round up to 10,

4. then the filter will filter the t with rank greater than 10, which mean the rows with 10 and 11, 

5. then we get the min value of total column in the filter, the value with the rank 10,

We make some modify with those two measures as following:

90th percentile = 
VAR t =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[ID],
        "Total", CALCULATE ( SUM ( 'Table'[$] ) )
    )
    var rt = ADDCOLUMNS ( t, "Rank", RANKX ( t, [Total],, ASC,Dense) )
RETURN
    MINX (
        FILTER (
            rt,
            [Rank]
                >= ROUNDUP (maxx(rt,[Rank])* 0.9, 0 )
        ),
        [Total]
    )

Visual Control = IF(Calculate(SUM('Table'[$])) <CALCULATE([90th percentile],ALLSELECTED('Table')),-1,1)

Best regards,