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.
Hi,
I have sales information per district with different filters about certain item classes and branch types within a district. I have margin, sales, and cost per bucket. I would like to find a "best in class" margin number based on a calculation that can filter with said item classes and branch types. This "best in class" margin has the logic of the total margin of the top fourth margin numbers in the company. Intuitively, I would say "if(total margin > percentile(total margin, .75), divide(sum(total sales) - sum(total cost), total sales)". How would i put together this measure correctly with DAX that will filter correctly?
Solved! Go to Solution.
@Anonymous
Not sure what's happened there - should be fine if you define the measure as described with the VAR/RETURN structure.
It's working in this pbix file:
https://www.dropbox.com/s/w9l7j0flmof3txj/Margin%2075th%20percentile.pbix?dl=1
Naming is slightly different from yours, but look at the measure on the card.
I thought something like this would be close at least.
Best in Class = CALCULATE([POS Margin],'Best in Class'[Total Margin]>[75th Percentile])
where POS Margin is a measure
POS Margin = divide(sum('Best in Class'[Total Sales])-sum('Best in Class'[Total Cost]),sum('Best in Class'[Total Sales]))
where Total Margin is a column,
and where [75th Percentille] is a measure
75th Percentile = PERCENTILE.INC('Best in Class'[Total Margin],.75)
I get the error: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
I also tried
Best in Class = CALCULATE([POS Margin],filter('Best in Class','Best in Class'[Total Margin]>[75th Percentile]))
which didn't have an error, but is not returning what looks like anything as far as values.
Hi @Anonymous
I believe you can use one of the PERCENTILEX functions.
To define the measure, you just need to define the 'granularity' you want to use to calculate percentiles.
For example, do you want percentiles calculated across all item class & branch type combinations, or separately for each branch across all item classes, or something else?
Might help if you could give an example of the calculation with dummy data.
Dummy data:
Margin Sales Cost Standard_Item
45% 100 55 Y
42% 120 70 Y
50% 140 70 Y
54% 130 60 Y
25% 60 45 N
40% 50 30 N
45% 100 55 N
43% 105 60 N
So I'd like to write something that ranks the margin by percentile and then calculates the new margin. So with no filter, I'd like it to take the 75th percentile of margin (50% & 54%) and then calculate the new margin in that percentile. divide(sum(sales)-sum(cost),sales). If it is filtered to a non standard item, it takes the 75th percentile of margin (45%) and calculates the margin in that percentile divide(sum(sales)-sum(cost),sales). Does this help?
@Anonymous
I see.
Without creating any intermediate measures, and making use of Margin percentage already in the table, you could create a measure like this (rename table as needed):
Margin % for 75th percentile and above = VAR Percentile75 = PERCENTILEX.INC ( YourTable, YourTable[Margin], 0.75 ) RETURN CALCULATE ( 1 - DIVIDE ( SUM ( YourTable[Cost] ), SUM ( YourTable[Sales] ) ), YourTable[Margin] >= Percentile75 )
Thanks for getting back to me. This still gives me the error: "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
How can I get around this?
@Anonymous
Not sure what's happened there - should be fine if you define the measure as described with the VAR/RETURN structure.
It's working in this pbix file:
https://www.dropbox.com/s/w9l7j0flmof3txj/Margin%2075th%20percentile.pbix?dl=1
Naming is slightly different from yours, but look at the measure on the card.
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 |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |