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
sabeensp
Helper IV
Helper IV

Sum IF Conditional

Hello,

I have a masure (Sum of Transation), but transaction can be in multiple or one currency. I only want to sho wthe sum, if customer did a transaction in single currency.

 

pbix can be downloaded from here:
PBIX File

 


Only show sum, if Currency in all transactions are the same:

For Example:

Customer B has only USD, then show the sum

Customer D has only GB then show the sum

 

But if  transaction is in multiple currencies, I don't want to show the sum. I would like to show " -" or "Multiple Currency" 

1 ACCEPTED SOLUTION

Tough to say, that boundary case wasn't in the data. Should just have to modify the RETURN a bit:

 

Measure = 
VAR __table = SUMMARIZE(Table1,[Currency])
VAR __count = COUNTROWS(__table)
RETURN
SWITCH(TRUE(),
  ISBLANK(__count),"No Data",
  __count = 1,SUM([Transaction Amount]),
  "Multiple Currencies"
)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

Try this:

 

Measure = 
VAR __table = SUMMARIZE(Table1,[Currency])
VAR __count = COUNTROWS(__table)
RETURN
IF(__count = 1,SUM([Transaction Amount]),"Multiple Currencies")

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_DecklerThanks, that solution partly worked, because I have Customer with no transaction in that table, with your solution they also showup in the Grid visual. It may be a simple fix, I don't know.

Tough to say, that boundary case wasn't in the data. Should just have to modify the RETURN a bit:

 

Measure = 
VAR __table = SUMMARIZE(Table1,[Currency])
VAR __count = COUNTROWS(__table)
RETURN
SWITCH(TRUE(),
  ISBLANK(__count),"No Data",
  __count = 1,SUM([Transaction Amount]),
  "Multiple Currencies"
)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.