Create new Table calculating Balances

[lcasey]

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

[v-haibl-msft]

@lcasey

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

[Greg_Deckler]

You should just have to filter your table visualization by Amount greater than 0