Hi,
I've encountered a complex problem, and would appreciate any insight anyone can give!
Basically, I'm trying to calculate the difference between the profit a store makes, and the value of the kth percentile of all stores across the country, with the kth percentile being a selectable variable. However, this needs to work across different sections (e.g. Clothes, Shoes, Household Items etc...), with the profit worked out individually for each section, and then totalled.
However, my current DAX formulas, shown below, seem to give a total as the profit difference between the store as a whole to the kth percentile of all stores in the country, e.g. it works out the total profit percentile, then compares to the total of the store, not for each individual section/category.
This measure works correctly on a visual which breaks it down by section, and then totals (e.g. the waterfall chart).
I guess it needs some GROUPBY style function, but I am not experienced enough with DAX to do this correctly - any advice will be much appreciated!
Current formula(s):
VAR CurrentPercentile =
IF (
HASONEVALUE ( 'Percentile'[Percent] ),
LASTNONBLANK ( 'Percentile'[Percent], 1 ),
0
)
VAR PercentileProfit
CALCULATE (
PERCENTILEX.INC (
SUMMARIZE (
'Sales'[Store],
'Sales'[Section],
"GroupedProfit", SUM ( 'Sales'[Profit] )
),
[GroupedProfit],
CurrentPercentile
),
ALL ( 'Sales'[Store] ),
ALL ( 'Sales'[Section] )
)
VAR ProfitDifference = PercentileProfit - SUM('Sales'[Profit])
RETURN ProfitDifference
