topic Re: Calculation with filter in DAX Commands and Tips

Calculation with filter

Anonymous — Tue, 18 Feb 2020 20:24:06 GMT

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

Re: Calculation with filter

az38 — Tue, 18 Feb 2020 20:34:04 GMT

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

Re: Calculation with filter

Anonymous — Tue, 18 Feb 2020 20:47:48 GMT

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

Re: Calculation with filter

az38 — Tue, 18 Feb 2020 20:55:36 GMT

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

Re: Calculation with filter

az38 — Tue, 18 Feb 2020 20:58:29 GMT

Anonymous

and how exactly do you want to multiple?

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

Re: Calculation with filter

Anonymous — Tue, 18 Feb 2020 21:12:34 GMT

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

Re: Calculation with filter

az38 — Tue, 18 Feb 2020 21:14:59 GMT

Anonymous

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

Re: Calculation with filter

Anonymous — Tue, 18 Feb 2020 21:23:12 GMT

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)

Re: Calculation with filter

az38 — Tue, 18 Feb 2020 21:26:33 GMT

Anonymous

what column contains names?

show data example, please

Re: Calculation with filter

Anonymous — Tue, 18 Feb 2020 21:34:27 GMT

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

Re: Calculation with filter

az38 — Tue, 18 Feb 2020 21:42:00 GMT

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

Re: Calculation with filter

Anonymous — Tue, 18 Feb 2020 21:52:18 GMT

Perfect!!. Thanks very much 😀