Dynamic division depending on chosen filters

[MrMano]

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!

[amitchandak]

@MrMano ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[MrMano]

Hi @amitchandak ,

I have created a sample PBIX with some random data that is of similar structure. I have pre-added the currently selected filters which belong to Store 1 (Asia + Location Strength A) where the division works correctly. If you change any of "location strength" or "region" you will see that it doesnt work anymore and it becomes blank.

Could you see if you come up with a good solution that works whatever filter value you choose?

https://drive.google.com/file/d/1-mPJ_S4P-T4QvSaRRen6o69f-OIpERqp/view?usp=sharing

Thanks in advance!!