topic DAX Exclude Bottom 5% sales and calculate 75th percentile of the top 95% sales in DAX Commands and Tips

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

Anonymous — Thu, 20 Oct 2022 02:19:11 GMT

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

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

Jihwan_Kim — Thu, 20 Oct 2022 03:28:40 GMT

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.

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

CNENFRNL — Thu, 20 Oct 2022 03:30:46 GMT

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

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

Anonymous — Thu, 20 Oct 2022 18:26:39 GMT

Thank you Jihwan_Kim