Solved: Perform SUM of values with running balance - Page 2

CPansewicz · 12-12-2023 09:25 AM

Hi, I am looking for assistance with configuring a running balance. I have been able to write the subtotal expression in my Related Invoices Table, but I am not sure what steps to take next to subtract from the total in the parent table. Also, my expression isn't quite right for this specific instance, since it subtotals all records for that request, instead of one by one.

My current expression in the Related Invoices Table is a virtual column to subtotal of invoices for that table:
[request_id].[req_total] - SUM(SELECT(Transaction[invoice_total], IN([request_id], LIST([_thisrow].[request_id]))))

What I would like to happen is that the Drawdown Balance column would show the running balance in this way:

Entry 1: [req_total] - [invoice_total] of Entry 1
Entry 2: [req_total] - [invoice_total] of Entry 1 - [invoice_total] of Entry 2
Entry 3: [req_total] - [invoice_total] of Entry 1 - [invoice_total] of Entry 2- [invoice_total] of Entry 3
.... etc ...

I hope that makes sense.

Related InvoicesTransactionsRequests

Landan_QREW

I am glad it is working.

One thing I will say is [_RowNumber] is really not a great idea for this use case, because row numbers for rows may change. It is much better to use a column that will not change like an invoice date or to turn your invoice number into an actual number by performing an expression like the following:
NUMBER(SUBSTITUTE([invoice_number],'Invoice ',''))

This would cause your final expression to look like this:

[po_id].[PO Amount] -
SUM(
SELECT(Transactions[invoice_total],
AND([po_id]=[_thisrow].[po_id], NUMBER(SUBSTITUTE([invoice_number],'Invoice ',''))<=NUMBER(SUBSTITUTE([_THISROW].[invoice_number],'Invoice ','')))
)
)

View solution in original post