cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrMano
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).

 

StoreLocation StrengthLocation TypeRegionSales
A34Asia3000
B45Asia3500
C54Asia4000
D43Americas5000
E35Amercias4000
F53EU5500
G44EU4500
H34EU3000

 

 

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
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1668478633366.png

 


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.

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1668478633366.png

 


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
Super User
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])

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.