After you join the 2 datasets on order_id sales field getting the total sales - 1000 and refund is - 600 should be pretty straightforward. For Original sale, create a calculated field in any of the 2 table : sum(sales)-sum(refunds). This field should give the required result which is 400.
Thank you Jas for your reply-.-. It was my bad that the initial data set was not the correct one, The actual data set would be
Will leftouter between sales + and refund on order id
data set would be
order_id Sales order_id(refund)
1 100 null null
2 200 2 Y
3 300 null null
4 400 4 Y
How to calculate following using calculated field
Now total sales - 1000
refund is - 600
Original Sale - 400
1 of 1 people found this helpful
In this scenario, first create a calculated field for refunds - if [Sales (Refund)]='Y' THEN [Sales] ELSE 0 END
Then create second calculated field for original sale using Sales and the newly created calculated field for refunds.