Create Dynamic Benchmarks that change depending on other filters

[MrMano]

Hi guys,

I'm trying to create a benchmarking tool where a user will choose a certain store and see its KPIs, which will then be compared to the KPIs showing the averages of all the stores in that region. Moreover, there are also other filters that should change these KPIs when applied, such as location strength, location type, brand, etc. 

For example, this is how the table look approximately (with many other columns like Sales that I need to do a similar calculation with).

Store	Location Strength	Location Type	Region	Sales
A	3	4	Asia	3000
B	4	5	Asia	3500
C	5	4	Asia	4000
D	4	3	Americas	5000
E	3	5	Amercias	4000
F	5	3	EU	5500
G	4	4	EU	4500
H	3	4	EU	3000

So for example, here I would like to choose store A and the benchmark should then show 3500 ((3000 + 3500 + 4000)/3).

I've tried a bunch of different options, but the problem is that when using the other filters the KPIs become blank. And I have thousands of rows, so shouldn't be a problem that there are no examples.

My calculation looks like this currently (which works fine when not using any other filters):

Benchmark sales = CALCULATE(

                                        AVERAGE(Financials[Sales]),

                                        SUMMARIZE(Financials, Financials[Region]),

                                        ALL(Financials[Store]))

So basically, I just want the measure to show the general average per region, and then the user should be able to choose values for location strength/type/brand etc and the average should change, depending on those selected values.

Any help is apprecaiated! Thanks!

EDIT: I now understand why it goes blank with other filters - currently if I select store A, which has a location strength of 3, the KPIs only work with the location strength filter is I set it to 3 as well, and if I choose 1, 2, 4, or 5, it becomes blank. What can I do to avoid this?

[v-yalanwu-msft · Accepted solution]

Hi, @MrMano ;

You could create a measure as follow:

Measure = CALCULATE(AVERAGE('Table'[Sales]),FILTER(ALLSELECTED('Table'),[Region]=MAX('Table'[Region])))

Or 

measure=CALCULATE(AVERAGE('Table'[Sales]),ALLEXCEPT('Table','Table'[Region]))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@MrMano, Try like

AverageX(filter(allselected(Financials), Financials[Region] =max(Financials[Region])),Financials[Sales])

or

AverageX(filter(all(Financials), Financials[Region] =max(Financials[Region])),Financials[Sales])