Hi,
Having some trouble figuring out a solution with Multi-Row functions to remove offsetting debit and credit transactions at an account level.
For instance, let's say account 12345 has 3 transactions, $250.50, -$250.50, $350.00
Can someone help me populate a formula that will only leave me the $350.00? I've tried sourcing the Community for similar examples but the formula I'm using bring me back $600.50($250.50+$350.00)
below is the formula I am using where txn_am is the transaction amount, and Net Amount is the new column
IF ISnull([Row-1:txn_am]) THEN [txn_am]
ELSE [Row-1:Net Amount] + [txn_am] ENDIF
to be more clear, what I need is to bring back 1) if the values are directly offsetting ($250, -$250), give me 0
2) give me all remaining positive values
Thanks in advance!
you don't want to use multi-row formula for offsetting transactions. you can summarize with whatever group-bys you need it will net the 250 and -250
I've tried that as well, but the second condition I should have specified is that I don't want it to net debits and credits that don't match
what the summarize function was doing was netting for example, +$350.00 with -$250.00 to only bring back the $100
to be more clear, what I need is to bring back 1) if the values are directly offsetting ($250, -$250), give me 0
2) give me all remaining positive values
Hope that is more clear. thank you
Sure - I'd posit that multi-row still isn't your way to go. What I'd probably do is a quick & dirty solution - ie. make a column of absolute values. get a count of the absolute value column vs the count of the non-absolute column value. If they counts don't match there are offsetting transactions and you can find them/exclude them...