cancel
Showing results for
Did you mean:
Frequent Visitor

Create Dynamic Benchmarks that change depending on other filters

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?

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

@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])

Announcements

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!