Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
How can I recreate such balances in DAX?
Thanks in advance!
Solved! Go to 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.
@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
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.
@Greg_Deckler , sure, here is the things you are asking:
1. Sample data:
Transaction_ID | Transaction_Date | Subaccount_from | Subject_from | Subaccount_to | Subject_to | Amount |
1 | 10.09.2021 | A | 1 | B | 2 | 6 001,81 |
2 | 10.09.2021 | B | 2 | E | 5 | 2 867,00 |
3 | 10.09.2021 | B | 2 | D | 3 | 2 133,00 |
4 | 10.09.2021 | D | 3 | F | 3 | 100,00 |
5 | 10.09.2021 | D | 3 | H | 3 | 100,00 |
6 | 10.09.2021 | D | 3 | I | 3 | 120,00 |
7 | 10.09.2021 | D | 3 | J | 3 | 100,00 |
8 | 10.09.2021 | D | 3 | K | 3 | 100,00 |
9 | 10.09.2021 | D | 3 | L | 3 | 100,00 |
10 | 10.09.2021 | D | 3 | M | 3 | 950,00 |
11 | 10.09.2021 | D | 3 | N | 3 | 33,00 |
12 | 12.09.2021 | G | 4 | D | 3 | 2 000,00 |
13 | 12.09.2021 | D | 3 | F | 3 | 100,00 |
2. Expected result - a visual / matrix with the following structure:
Subaccount_name** | Balance* |
A | -6001,81 |
B | 1001,81 |
C | 0,00 |
D | 2430,00 |
E | 2867,00 |
F | 200,00 |
G | -2000,00 |
H | 100,00 |
I | 120,00 |
J | 100,00 |
K | 100,00 |
L | 100,00 |
M | 950,00 |
N | 33,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:
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])
@Now92Jah See Page 4 of attached PBIX.
@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.
@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.
@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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |