Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
It's nothing but just 75%*95%=71.25% percentile of the whole set.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |