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
Now92Jah
Frequent Visitor

Transactions flow - Modified SUMIF in DAX, based on category

Hello, 

I have A following table of transactions. They can be made inside or outside the organization. If it is inside, there can be cash transfers between subaccounts. How can I show the balance of each subaccount in a matrix? 

In Excel it is simply SUMIF (amount from the source) - SUMIF (amount from the recipient) --> it is showed in those two columns on the right of the screen. 

Now92Jah_1-1631786178985.png

How can I recreate such balances in DAX? 

Thanks in advance!

 

1 ACCEPTED SOLUTION

@Now92Jah I would recommend a separate table for the accounts. You could create this in Power Query or in DAX like:

 

Accounts Table = 
  DISTINCT(
    UNION(
      SELECTCOLUMNS('Table',"Account",[Subaccount_from]),
      SELECTCOLUMNS('Table',"Account",[Subaccount_to])
    )
  )

 

You would then modify the measure:

 

Measure 2 = 
    VAR __Account = MAX('Accounts Table'[Account])
RETURN
    SUMX(FILTER(ALL('Table7'),[Subaccount_to]=__Account),[Amount]) - 
    SUMX(FILTER(ALL('Table7'),[Subaccount_from]=__Account),[Amount]) + 0

In your visual, use the measure and the Account column from Accounts Table.

 


@ 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

12 REPLIES 12
Greg_Deckler
Super User
Super User

@Now92Jah You can use SUMX(FILTER(...),...) or CALCULATE with filter clauses to emulate SUMIF in Excel. I wrote a whole series of blog posts on Excel to DAX: Excel to DAX Translation - Microsoft Power BI Community


@ 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...

Hello @Greg_Deckler , 
Thank you for your reply. 

But I need to apply it to the whole group of subaccounts, not to each one individually. So that the matrix is created. 
Using filter shows blanks in the matrix. 

@Now92Jah Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ 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_Deckler , sure, here is the things you are asking: 

1. Sample data: 

Transaction_IDTransaction_DateSubaccount_fromSubject_fromSubaccount_toSubject_toAmount
110.09.2021A1B26 001,81
210.09.2021B2E52 867,00
310.09.2021B2D32 133,00
410.09.2021D3F3100,00
510.09.2021D3H3100,00
610.09.2021D3I3120,00
710.09.2021D3J3100,00
810.09.2021D3K3100,00
910.09.2021D3L3100,00
1010.09.2021D3M3950,00
1110.09.2021D3N333,00
1212.09.2021G4D32 000,00
1312.09.2021D3F3100,00

 

2. Expected result - a visual / matrix with the following structure:

 

Subaccount_name**Balance*
A-6001,81
B1001,81
C0,00
D2430,00
E2867,00
F200,00
G-2000,00
H100,00
I120,00
J100,00
K100,00
L100,00
M950,00
N33,00

 

*Balance - shows the flow of transactions between subaccounts. Which means, we have to add to the balance all incomes, and substract all outcomes. In Excel I use SUMIF two times. 
**Subaccount_name - it is a list from another table (TB_Subaccounts), where there are only uniue names of each subaccount, connected via relationship with the TB_Transactions table. 

Here is a full relationship diagram, if you need, with the main part described by colorful frames:

Now92Jah_1-1631802157005.png

 

Also, I checked those links and I cannot find there solution for my problem. 



@Now92Jah Try:

Measure 2 = 
    VAR __Account = MAX('Table7'[Subaccount_from])
RETURN
    SUMX(FILTER(ALL('Table7'),[Subaccount_to]=__Account),[Amount]) - 
    SUMX(FILTER(ALL('Table7'),[Subaccount_from]=__Account),[Amount])

@ 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_Deckler , unfortunately, the result is only blanks in the matrix. 

@Now92Jah See Page 4 of attached PBIX. 


@ 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_Deckler , thanks for the file. 
This shows only the balance on the acounts from where the transactions goes, whereas I need balances for all acounts. In page 4 there are only 4, whereas in the table we have 13 accounts in total. 

@Now92Jah I would recommend a separate table for the accounts. You could create this in Power Query or in DAX like:

 

Accounts Table = 
  DISTINCT(
    UNION(
      SELECTCOLUMNS('Table',"Account",[Subaccount_from]),
      SELECTCOLUMNS('Table',"Account",[Subaccount_to])
    )
  )

 

You would then modify the measure:

 

Measure 2 = 
    VAR __Account = MAX('Accounts Table'[Account])
RETURN
    SUMX(FILTER(ALL('Table7'),[Subaccount_to]=__Account),[Amount]) - 
    SUMX(FILTER(ALL('Table7'),[Subaccount_from]=__Account),[Amount]) + 0

In your visual, use the measure and the Account column from Accounts Table.

 


@ 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_Deckler , thanks, I am a step closer to the result. 

Actually, I already have the table with all the subaccounts, called TB_Subaccounts. 
So changing the measure to that table gives the extended result. 

But on the other hand, this measure shows only the balances of the accounts, on which there were made any transactions. 
Is there any modification, to show all the balances, even on subaccounts without transactions (yet) on them? 

@Now92Jah Well that was the purpose of add the +0 on the end to force the visual to return balances with no transactions. If you have a separate table with current balances, you would just need to grab that value and then add that to the RETURN statement as + [existing balance].

 

I feel like I am working with half the information here so not sure.


@ 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_Deckler , not precisely - in my model I have only the table with the names of subaccounts, not balances. 
But the trick with adding 0 worked, after some modifications in my model. Thank you! 

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.