Could you please make mocked up data, we don't need actual data at all, but need to know data structures at least.
As per Shin's comment, we need mock up data to help you better. It's difficult to provide solution on the basis of description. That said, based on what I understood from your description, I came up with the solution. Not sure this works or not for you. You can try the below formula:
This a LOD calculation, depending on your view you might need to change the formula. Also, if there are filters in the view, try adding them to context.
Yes! This is definitely on the right track. Seems to be doing what I want anyway. The only piece missing is it doesn't seem to want to filter by "Transaction Type". Should I be adding something in the calc to accommodate for this?
You can try adding this filter to context.