DAX Exclude Bottom 5% sales and calculate 75th percentile of the top 95% sales

[lefli85]

Hello,

I need help with DAX that calculate the 75th percentile of sales the top 95% salespeople by sales. I have attached a Excel snippet of expected results. Sales highlighted in yellow need to be excluded from the table of users that will be used to calculate the 75th percentile. I can calculated the percentile in DAX but stuck on filtering out the bottom 5% salespeople

--sales percentile
VAR Percentile75 = 0.75
 --bottom 5% salespeople to be excluded
VAR SalespeopleExcluded = 0.05
--selected state
VAR StateinContext =
    SUMMARIZE ( 'Sales', 'Sales'[State] ) 

--total sales for entire state
VAR TotalSales =
    CALCULATE (
        [Sales],
        StateinContext,
        ALL('Sales'[Salesperson]),

    )
VAR SalesBySalesperson =
    FILTER (
        CALCULATETABLE (
            ADDCOLUMNS (
                CALCULATETABLE (
                    SUMMARIZE (
                        'Sales',
                        'Sales'[Salesperson]

                    )
                ),
                "@SalespersonProportionofSales%", CALCULATE ( DIVIDE ( [Sales], TotalSales ) ),
                "@Sales", [Sales]
            ),
            ALL ( 'Sales'[Salesperson] )
            ,StateinContext
        ),
        [@SalespersonProportionofSales%] > SalespeopleExcluded
            && [@Sales] <> BLANK ()
    )
VAR Result =
    CALCULATE (
        PERCENTILEX.INC (
            SalesBySalesperson,
            [@Sales],
            Percentile75
        )
    )
RETURN
Result

[CNENFRNL]

It's nothing but just 75%*95%=71.25% percentile of the whole set.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your datamodel.

All measures are in the attached pbix file.

In the below, by using the measure [Percentage cumulate except bottom 5%:], the desired result can be shown.

[lefli85]

Thank you @Jihwan_Kim