Create Measure that calculates sum 1 of 2 ways based on client

[jrsommer]

Hello all!

I feel like this should be 'straightforward' but perhaps I'm just new enough to be struggling with this. 

I need to create a measure that SUMS my 'Cost' field. However the needed calculation will vary for 1 particular client. Hopefully this helps explain what I mean/need:

IF ([ClientName] = "A"

SUM('table'[Cost]),'table'[Type] ="Cloud",'table'[ClientName]="A") + 
SUM('table'[Cost]),'table'[Type] ="Cloud",'table'[ClientName]="B")*.35

ELSE

CALCULATE(SUM('table'[Cost]),'table'[Type] ="Cloud")

 I dont expect that syntax to be at all correct, I was just trying to type it out so the different parts are clear. 

 FOR WHAT ITS WORTH: In Excel, I calculated this by the following: (This was only entered in the client specifc cell)
=SUMIFS(R:R,A:A,"A",B:B,"Cloud")+(SUMIFS(R:R,A:A,"B",B:B,"Cloud",C:C,"Engineering",D:D,"Azure")*0.35)

Thank you for your help!

[amitchandak · Accepted solution]

@jrsommer , Test as

IF (max([NombreDeUsuario)] - "A"
calculate(SUM('table'[Cost]),filter('table'[Type] '"Cloud",'table'[ClientName]-"A")) +
calculate(SUM('table'[Cost]),filter(all('table'[ClientName]) ,'table'[Type] '"Cloud",'table'[ClientName]'"B"))*.35
,CALCULATE(SUM('table'[Cost]),'table'[Type] ?" Cloud"))

[jrsommer]

i think this is what i'm looking for. Need to do some data clean up and then i'll test it out.
FYI: Syntax had to be corrected when i typed the formula out, so hopefully i got it all correct.

Thank you!

[AllisonKennedy]

Try as a new MEASURE:

Total Cost = 

IF (SELECTEDVALUE([ClientName])= "A"
, SUMX(FILTER(ALL('table','table'[Type] ="Cloud" && table[ClientName]="A"), table'[Cost])  + 
SUMX(FILTER(ALL('table'),'table'[Type] ="Cloud" && table[ClientName]="B"), table'[Cost])*.35

, CALCULATE(SUM('table'[Cost]),'table'[Type] ="Cloud")

)

NOTE: I haven't changed your CALCULATE statement, but you could use a SUMX here too if you want, and wasn't sure if that should be for only selected client.

Another note, how is this going to be used? It will only work if only 1 client is selected in context (so client name must be in the visual or must have a filter for 1 client only).

You could use SUMX to change that and improve if you want.

[jrsommer]

Thank you so much for you assistance but i wil not be filtering/selecting a client on this page