Calculation with filter

[Anonymous]

I have a measure that I'm multipying by 2% on last year for sales managers.  I want to exclude 3 sale managers names from that  calculation.  My formula does not work.

test = CALCULATE(SUM([YAGO Combined Volume])*1.02, 'RSM Performance'[RSMP YAGO Volume]<>"Russell Curtis", 'RSM Performance'[RSMP YAGO Volume]<>"Jarrod Patrick", 'RSM Performance'[RSMP YAGO Volume]<>"Nigel Smith")

Any advice is greatly appreciated

[az38]

Hi @Anonymous 

try SUMX()

test = CALCULATE(SUMX(Table, Table[YAGO Combined Volume])*1.02, 'RSM Performance'[RSMP YAGO Volume]<>"Russell Curtis", 'RSM Performance'[RSMP YAGO Volume]<>"Jarrod Patrick", 'RSM Performance'[RSMP YAGO Volume]<>"Nigel Smith")

[Anonymous]

I've tried the formula below but giving an error message

[az38]

@Anonymous 

pardon, my prev statement has a mistake plus try FILTER

test = CALCULATE(SUMX('RSM Performance', 'RSM Performance'[YAGO Combined Volume]*1.02), FILTER(ALL('RSM Performance'), 'RSM Performance'[RSMP YAGO Volume]<>"Russell Curtis" && 'RSM Performance'[RSMP YAGO Volume]<>"Jarrod Patrick" && 'RSM Performance'[RSMP YAGO Volume]<>"Nigel Smith"))

[Anonymous]

I've tried that but when I add the measure to my report, I get gray box with details

[az38]

@Anonymous 

Are you sure you want to compare 'RSM Performance'[RSMP YAGO Volume] column with names? how is your data look like?

[Anonymous]

I don't want to compare 'RSM Performance'[RSMP YAGO Volume] column with names, but just exclude the 3 names from the volume calculation in the report  (ie. for those 3 names, the caculation wont have an effect)

[az38]

@Anonymous 

what column contains names?

show data example, please

[Anonymous]

for example: all YAGO volume is multiplied by 2% to get the AOP Target - what I am trying to do is have the 3 names (Russell Curtis and Jarrod Patrick and Nigel Smith) in the Sales Manager column be excluded from the AOP Target of 2% so having their targets showing no change to that in the TY Volume column

[az38 · Accepted solution]

@Anonymous 

maybe you need just smth like

Measure =
var _multiplier = IF(SELECTEDVALUE('RSM Performance'[Sales Manager]) = "Russell Curtis" || SELECTEDVALUE('RSM Performance'[Sales Manager]) = "Jarrod Patrick" || SELECTEDVALUE('RSM Performance'[Sales Manager]) = "Nigel Smith", 1, 1.02 )

RETURN
_multiplier * calculate(sum('RSM Performance'[YAGO Volume]))

?

[Anonymous]

Perfect!!. Thanks very much 😀

[az38]

@Anonymous 

and how exactly do you want to multiple?

do you want to multiply each value and sum results or multiply sum of column?