cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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)

1 ACCEPTED SOLUTION
Super User

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

4 REPLIES 4
Super User

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

Frequent Visitor

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!

Super User

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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors