SUM VALUES HIGHER THAN PERCENTILE

[babajat]

Hi!

I have a table with many rows, each corresponding to one order identified via "ID". Each order has the "NAME" of the person that ordered it. "NAME" is a value that can be repeated between orders.

I managed to get a formula that gives me the 90th percentile of orders grouped by person:

PERCENTILE = 
VAR SummaryTable =
    SUMMARIZE (
        'table',
        table[Name],
        "Number", DISTINCTCOUNT (table[ID])
    )
RETURN
PERCENTILEX.INC (
        SummaryTable,
        [Number],
        0.90
    )

Now I would like to get the sum of orders placed by people who ordered above the 90th percentile.

I tried with the following formula but "SUM" can't find the "NUMBER" column. I've been trying to solve this problem for hours...

TOT OVER PERCENTILE = 
VAR SummaryTable =
    SUMMARIZE (
        'table',
        table[Name],
        "Number", DISTINCTCOUNT (table[ID])
    )
var percentile =     PERCENTILEX.INC (
        SummaryTable,
        [Number],
        0.90
    )
RETURN
CALCULATE(SUM(Number), FILTER(SummaryTable, number > percentile))

Thanks in advance! 

[babajat]

Nevermind, i've just managed to do it with SUMX and FILTER:

TOT Over Percentile = 
VAR SummaryTable =
    SUMMARIZE (
        'TABLE',
        TABLE[Name],
        "Number", DISTINCTCOUNT (Table[ID])
    )
var percentile =     PERCENTILEX.INC (
        SummaryTable,
        [Number],
        0.90
    )
RETURN
SUMX (FILTER(SUMMARIZE ( Table, [Name], "Number1", DISTINCTCOUNT( Table[ID] )), [Number1] > percentile),
    [Number1])

I imagine it's a "dirty" solution so if anyone has any better ideas I'm open to suggestions.

Thanks anyway!

[v-rzhou-msft · Accepted solution]

Hi @babajat ,

I think you can update your measure as below.

TOT Over Percentile 1 =
VAR SummaryTable =
    SUMMARIZE ( 'TABLE', [Name], "Number", DISTINCTCOUNT ( 'TABLE'[ID] ) )
VAR percentile =
    PERCENTILEX.INC ( SummaryTable, [Number], 0.90 )
RETURN
    SUMX ( FILTER ( SummaryTable, [Number] > percentile ), [Number] )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[babajat]

Much cleaner, thank you!