Forum Discussion

MrMano's avatar
MrMano
Frequent Visitor
3 years ago
Solved

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?

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.