Fixing the measure that responds to slicer

[anwarbi]

Hi,

I have created the below measure,  so that if i select  a 'vendor name' from the drop down slicer, it should subtract the average score (of the selected vendor from the slicer) from the peer average score (the second part of the measure which adds a filter of related peers- which are also a group of vendors).

The result that i am getting however is incorrect as it only gives me the average score of the selcted vendor in the output and does not consider subtractraction. 

I think this measure needs to add some variable in the begining so that the measure responds to the selected vendor from the slicer. 

Score deviation with peer avg. = AVERAGE(Sheet1[Score])- (CALCULATE(AVERAGE(Sheet1[Score]), Sheet1[related Peers]<>""))

Your help will be appreciated.

[amitchandak]

@anwarbi , if that means blank is should is not(isblank([related peer]))

example

Score deviation with peer avg. = AVERAGE(Sheet1[Score])- (CALCULATE(AVERAGE(Sheet1[Score]),filter(all(Sheet1),  not(isblank([related peer]))  && [related peer] <> "" ))

[anwarbi]

Thanks Amit for your help.

Score deviation with peer avg. = AVERAGE(Sheet1[Score])- (CALCULATE(AVERAGE(Sheet1[Score]),filter(all(Sheet1),  not(isblank([related peer]))  && [related peer] <> "" ))

The above measure you suggested kind of works with the slicer, however it calculates an aggregate average value. so if i create a matrix table then all the cells in the table are populated with similar value.

In a matrix table the average values should correspond to the parameter in the row and column.

I beleive we are using All function in the measure, which might be causing this issue.

below is the example of the output that i am getting with the current measure and another table shows what i should get.

e.g. Current output

	Location A	Location B	Location C
Prod A	6.9	6.9	6.9
Prod B	6.9	6.9	6.9
Prod C	6.9	6.9	6.9

e.g. Expected output

	Location A	Location B	Location C
Prod A	5.2	6.5	5.7
Prod B	6.9	5.3	6.2
Prod C	6.1	5.9	6.7

Thanks.

[Anonymous]

Hi @anwarbi 

I've updated your code to make it easier to understand.

Score deviation with peer avg. = 
VAR _Part1 = AVERAGE(Sheet1[Score])
VAR _Part2 = CALCULATE(AVERAGE(Sheet1[Score]),filter(all(Sheet1),  not(isblank([related peer]))  && [related peer] <> "" )
RETURN
_Part1 - _Part2

Your measure [Score deviation with peer avg. ] is calculated by two parts.  Only _Part2 is filtered by ALL function. _Part2 will return the same result in your Matrix, but _Part1 won't. So I am confused about why your measure will always return 6.9.

And the filter in _Part2 not(islbank...) is the same as [related peer] <>"". You just need one of them. 

If you want _Part2 won't be filtered by Vendor slicer, you can update your code as below.

Score deviation with peer avg. = 
VAR _Part1 = AVERAGE(Sheet1[Score])
VAR _Part2 = CALCULATE(AVERAGE(Sheet1[Score]),filter(all(Sheet1),[related peer] <> ""&&Sheet1[Prod]=MAX(Sheet1[Prod])&&Sheet1[Location]=MAX(Sheet1[Location]))
RETURN
_Part1 - _Part2

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[anwarbi]

Thanks Rico. 

I will test your solution, however i did not understand the below addition in the measure, why it is needed.

Sheet1[Prod]=MAX(Sheet1[Prod])&&Sheet1[Location]=MAX(Sheet1[Location]))

[Anonymous]

Hi @anwarbi 

This is to let _Part2 to calculate the average by filter Prod and Location. Maybe I have misunderstanding in your requirement. Please share a sample with me and show me a screenshot to show me the result you want. I need to know your data model to understand your calculate logic.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@anwarbi , Based on what I got, it should be

Score deviation with peer avg. = AVERAGE(Sheet1[Score])- (CALCULATE(AVERAGE(Sheet1[Score]),all() ))

[anwarbi]

Thanks Amit,

However, I think the solution does not consider the (Sheet1[related Peers]<>"") filter that i have added in the measure. 

Also, I forgot to mention that this measure would be used a rule for conditional formatting a table.

Thanks.