Forum Discussion
Filter measure
Hey,
Is the Description column unique to each purchase / refund? If so, you could do something in DAX like:
SWITCH(TRUE()
CALCULATE(COUNT(description), ALLEXCEPT(Table, [Description]) > 1 &&
CALCULATE(SUM([Transaction Amount]), ALLEXCEPT(Table, [Description]) > 0, "Partial Refund",
CALCULATE(COUNT(description), ALLEXCEPT(Table, [Description]) > 1 &&
CALCULATE(SUM([Transaction Amount]), ALLEXCEPT(Table, [Description]) = 0, "Full Refund",
BLANK()
)To explain the logic behind the code - the COUNT(description) looks if the same product has 2 entries (assuming that each description is unique, then if the person purchases another of the same product, then there would be some kind of increment counter in the description. This is a massive assumption though). And then summing the amount - if it's a partial refund, then the amount paid should be greater than the amount refunded, hence the >0 condition.
Hope that makes sense.
vicky_
The Discription column is typically the same ("Uber" charge me $10, "Uber" refunded me -$10). Normally, Discription (Vendor) would be on both Debit and Refund transactions.
OMG - thank you again Vicky. You're awesome. I think i can work with what you advised.
Any suggestion if not unique?
Discription (Vendor) would be every incurred transaction. Debit and Refunds. Typically, there would be more debited and refund. I would think the measure would not be accurate (inflating partial refund)
Nope, unfortunately if there's no unique description then it would be very difficult to tell which transactions are related. Either get a column that's unique or think of other ways to link the transactions (e.g. combine date and description or something like that)