Perform SUM of values with running balance

[CPansewicz]

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:

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

I hope that makes sense.

Related InvoicesTransactionsRequests

[Landan_QREW]

[request_id].[req_total] - SUM(SELECT(Transaction[invoice_total], AND([request_id]=[_thisrow].[request_id], [invoice_number]<=[_THISROW].[invoice_number])))

You may be able to sub a date column instead of doing the 'invoice_number' column as your invoice_number column may not be a numeric value.

[CPansewicz]

Hi, Thank you for your time on this. Yes, this expression works. There is one issue, when I build the expression in the App Formula window, and test the expression, the data looks good. When I save the table, and sync the app though, the data does not show in the app.

[Landan_QREW]

Which expression did you end up using?

[CPansewicz]

Hi,

Thanks for following-up on this. I have been working through a few issues.

I ended up using:
[request_id].[req_total] -
SUM(
SELECT(Transaction[invoice_total],
AND([request_id]=[_thisrow].[request_id], [_RowNumber]<=[_THISROW].[_RowNumber])
))

I'm not sure what I changed, but the data started populating the view correctly.

But, then I realized that this data should be related to the PO table, and not the Requests table. So I added a new table that was related to the PO table, using the same type of expression. After several syncs, the data is not showing up in that view - ha ha. So weird. The expression I am using on that table is:

[po_id].[PO Amount] -
SUM(
SELECT(Transactions[invoice_total],
AND([po_id]=[_thisrow].[po_id], [_RowNumber]<=[_THISROW].[_RowNumber])
)
)

[Landan_QREW · Accepted solution]

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 ','')))
)
)

[CPansewicz]

Yes. I was thinking that same thing, but then was thinking that maybe magically the row number wouldn't change (ha ha). 

My question about the invoice number is:

• what if there are two of the same invoice numbers
• what if the Invoice number has letters included - will the number function take care of that?

Thank you again!

[Landan_QREW]

Are you generating the invoice numbers?

Could you have a timestamp_created column and use that instead?

[CPansewicz]

Hi, Thank you! No, I am not generating invoice numbers, this is really only for data entry. I can convert the invoice number field from text to number in the table itself. Thank you so much again. I really appreciate the support community members give. I will mark this as solved.

[CPansewicz]

Well, I have it figured out, but I am not sure why. I deleted the bottom empty rows of the associated spreadsheet and the data is working fine in the POs table now.