cancel
Showing results for 
Search instead for 
Did you mean: 
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!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@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!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@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!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@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!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

@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!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!