Please find my solution by inner join your data to itself as shown below. A sample workbook attached for your reference.
Hope this helps
Book1_v10.2.twbx 11.2 KB
Thanks, ZZ. That is a good workaround.
I really love using sets, so I found this approach:
Create calculated field "new_transaction_id":
if transaction_type="Sale" then transaction_id
elseif transaction_type="Refund" then original_transaction_id end
Then create a set using that calculated field instead.