Forum Discussion
Create new Table calculating Balances
Hello,
Does anyone know how to create a table in Power BI by calculating the balance of customers?
I have a customer table, it contains Invoices and payments. If you Subtract the payments from the invoice that is the current balance.
So what formula woud I Use to create a new table with only customers that have a balance?
In the table below I have several Customers, only 1 has a balance. I want to create a table that ONLY has that 1 customer with a balance.
| CUSTNMBR | DOCNUMBR | DOCDATE | CURNCYID | CCURNCYID | PP | TRXDSCRN | Original Amt USD | Amount | |
| ZWARTWOUD | SLS003269 | 4/4/2007 0:00 | EUR | Net 30 | SETT NL | $10,021 | $10,021 | ||
| ZWARTWOUD | 22054 | 9/7/2007 0:00 | EUR | Net 30 | SETT-NL 7,500 | $0 | ($10,021) | ||
| ZYDOWICZ | SLS008123 | 3/31/2009 0:00 | SLS008123 | USD | USD | Net 30 | SETT-POLAND 1,500 | $500 | $500 |
| ZYDOWICZ | 32134 | 11/17/2009 0:00 | USD | USD | Net 30 | SETT-POLAND 1,500 | $0 | ($500) | |
| NEST INC | 52232 | 7/1/2013 0:00 | JPY | JPY | Net 30 | ENF EXP JPY 475,744 | $0 | ($4,800) | |
| NEST INC | SLS018077 | 7/1/2013 0:00 | JPY | JPY | Net 30 | SETT JPY 2,547,109 | $25,700 | $25,700 | |
| NEST INC | 52429 | 8/6/2013 0:00 | USD | JPY | Net 30 | PMT JPY 2,071,365 | $0 | ($20,900) | |
| TEST | SLS99999 | 10/1/2013 0:00 | USD | JPY | Net 30 | PMT JPY 2,071,365 | $0 | $5,000 |
2 Replies
You should just have to filter your table visualization by Amount greater than 0
I’m not sure which columns in your provided table are Invoices and Payments, so I just test with following table. You can create a measure for Balance first and then use CALCULATETABLE Function to create a table only includes customers with balance.
Balance = CALCULATE ( SUM ( Table1[Invoices] ), ALLEXCEPT ( Table1, Table1[Customer] ) ) - CALCULATE ( SUM ( Table1[Payments] ), ALLEXCEPT ( Table1, Table1[Customer] ) )
HasBalance = CALCULATETABLE ( Table1, FILTER ( Table1, [Balance] > 0 ) )
Best Regards,
Herbert
