Newbie - Calculating percentiles after filters applied

[wokka]

Hi,

So this is a newbie question and I cant share my data unfortunately. 

What I'm trying to do is trying to calculate 80th percentile on filtered data ( the filtered data is used to generate a graph ).

I can run a query on the source ( unfiltered ) data to calculate a percentile easily enough ( see below ), but I am unsure on how to access and use the filtered data thats used to create the graph. I guess I'm asking how to put the filtered data into a temporary table so I can run code against it.

This is the code I have to generate the percentile figure for the underlying unfiltered data :

Measure_80Perc =

            var entry_details =

                calculatetable (

                    summarize ( '<col1>', <PK>, <main_column_with_data> ),

                    keepfilters ( not  isblank ('<col1>'<main_column_with_data>  ))

                )

            return PERCENTILEX.EXC ('<col1>' ,<main_column_with_data> , 0.80)

Any thoughts appreciated. 

[shafiz_p · Accepted solution]

Hi @wokka 

Since don't know about your data. I have tried to implement this using adventure works. 

Percentiles are typically calculated on raw datasets rather than summarized values. The goal is to determine the value below which a given percentage of observations in a dataset falls. When calculating percentiles, it’s important to ensure that the calculation is performed on the actual data points rather than aggregated or summarized values.

I want to find out 80th percentile for each month with respect to filter applied as well as without filter. Try same as below code:

Percentile80 = 
CALCULATE(
    PERCENTILEX.EXC(
        ALLSELECTED(FactResellerSales),
        FactResellerSales[SalesAmount],
        0.8
    ),
    VALUES('Calendar'[MonthName])
)

This will ensure the 80th percentile for sales amount of each month with respect to slicer. See outputs:

Without filter:

With filter:

However, if you really want it to summarize, then you can try code below:

Percentile80 = 

VAR FilteredTable =
SUMMARIZE(
    FactResellerSales,
    'Calendar'[MonthName],
    FactResellerSales[SalesAmount]
)

VAR Percentile = 
CALCULATE(
    PERCENTILEX.EXC(
        FilteredTable,
        FactResellerSales[SalesAmount],
        0.8
    ),
    ALLSELECTED(FactResellerSales),
    ALLSELECTED('Calendar')
)

RETURN
Percentile

 This will first summarize sales table by month and use that filter table to find out percentile with respect to filter as well as without respect to filter.

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[FreemanZ]

hi @wokka ,

your measure can accept the fitler context from other relevant visuals, like a slicer. 

you may also introduce fitler contexts with filter arguments like:

Measure_80Perc =

            var entry_details =

                calculatetable (

                    summarize ( '<col1>', <PK>, <main_column_with_data> ),

                    FILTER(....),

                    keepfilters ( not  isblank ('<col1>'<main_column_with_data>  ))

                )

            return PERCENTILEX.EXC ('<col1>' ,<main_column_with_data> , 0.80)