Hi,
I'm trying to create a benchmark tool where a user chooses a store to see its KPIs and then compare it to a benchmark KPI as an index number (100 = same value), which is dependent on which filter you choose. These categories can be things like "Location Strength", "Market", "Location Type", etc.
This is what I have currently, which works only in a certain filter context:
Delta market TO % = DIVIDE(MAX(Financials[TO%]), [Benchmark Market TO%])*100
with Benchmark Market TO % = CALCULATE( [Avg TO % - no blanks],
SUMMARIZE(Financials, Financials, [Market]),
ALL(Financials[Store]))
For example, if I choose a store that has Location Strength = 3 as a base store, the division works fine if I also put the Location Strength filter = 3. But as soon as I change any filter outside of the values of the chosen store (e.g. putting the Location Strength filter to 4 to compare to the benchmark of those stores), my delta market TO% measure becomes Blank. This is weird because my selected store value is static, and the benchmark value is dynamic and changes correctly with different filters. It's just the division that refuses to work.
Where do I go wrong here? Do I need to use variables? I've tried using formulas such as ALLEXEPCT and also things like filter interactions, but nothing seems to work. Any help is appreciated!
Thanks in advance!
