I have a business process (and dataset) where something like this can happen;
Day 1 - Customer purchases something for $1000
Day 2 - Customer returns that thing for a refund of -$1000
In my dataset I have;
- Transaction Date
- Store ID
- Customer ID
- Purchase Price
- Refund Price
What I would like to do is "Show me all the Customers who bought (net) more than zero" but if i do something like;
Count (Distinct ( IF (Rangesum ([Purchase Price] , [Refund Price]) > 0 , [Customer ID] , Null()))
Then it counts each individual row and will include any customer who subsequently returned their purchase on another day (it tests each individual row and then summarise).
The 'obvious' answer is to do an aggregation, but this gives inconsistent (and, to me, illogical results);
Count ( Distinct ( IF (
RANGESUM ( AGGR ( RANGESUM (([Purchase Price] , [Refund Price]) , [Customer ID])) > 0 ,
[Customer ID] , Null()))
Where am I going wrong? (It sometimes looks it might be only testing the first data line in the load and ignoring the subsequent ones for that client - any ideas???)
