Removing offsetting debits/credits (need help!)

Question

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!

apathetichell · Answer

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...

kswish2116 · Answer

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

apathetichell · Answer

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