Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculation with filter

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

 

 

 

 

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

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

?

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

11 REPLIES 11
az38
Community Champion
Community Champion

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

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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

 

Capture.PNG

 
az38
Community Champion
Community Champion

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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

Capture.PNG

az38
Community Champion
Community Champion

@Anonymous 

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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
Community Champion
Community Champion

@Anonymous 

what column contains names?

show data example, please


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

Capture.PNG

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
Community Champion
Community Champion

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

?

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

Perfect!!. Thanks very much 😀

az38
Community Champion
Community Champion

@Anonymous 

and how exactly do you want to multiple?

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors