cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Caide Frequent Visitor
Frequent Visitor

Returning amount refunded from purchase in store but refund happens in either same store or another

Hi, i have calculation issue i've been trying to solve with different solutions from this forum but none have really worked out for me.

Let's say my table looks like the one below and i want to find out the amount refunded originating from purchases made in store 1 - both refunds that happened in the same store but also in a different store?

 

ReceiptStoreTrans_typeAmountQuantityDate
55451Purchase29912018-11-20
55451Purchase49912018-11-20
55451Purchase179912018-11-20
55451Purchase19912018-11-20
55622Purchase19912018-11-21
55141Purchase34912018-11-22
55141Purchase49912018-11-22
55455Refund-299-12018-11-26
55455Refund-499-12018-11-26
55622Refund-199-12018-11-26
55143Refund-499-12018-12-04
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: Returning amount refunded from purchase in store but refund happens in either same store or anot

Hi @Caide

 

Try the following, where Table1 is the name of the table you show. It will give you the amount refunded from purchases originated in each store.

 

1. Set Table1[Store] in the rows of a matrix visual

2. Set this measure in values of the visual

 

RefundsFromPurchasesInStore =
VAR _ReceiptsList =
    CALCULATETABLE (
        DISTINCT ( Table1[Receipt] );
        Table1[Trans_type] = "Purchase"
    )
RETURN
    SUMX (
        CALCULATETABLE (
            Table1;
            _ReceiptsList;
            Table1[Trans_type] = "Refund";
            ALL ( Table1 )
        );
        Table1[Amount] * Table1[Quantity]
    )

Code formatted with   www.daxformatter.com

 

 

Super User
Super User

Re: Returning amount refunded from purchase in store but refund happens in either same store or anot

@Caide

 

Yeah, if you do a minor modification to the measure above, removing the ALL ( Table1 ) in the second CALCULATETABLE, you bring back the restriction to look in the same store:  

 

RefundsInLikeStore =
VAR _ReceiptsList =
    CALCULATETABLE (
        DISTINCT ( Table1[Receipt] );
        Table1[Trans_type] = "Purchase"
    )
RETURN
    SUMX (
        CALCULATETABLE (
            Table1;
            _ReceiptsList;
            Table1[Trans_type] = "Refund"
        );
        Table1[Amount] * Table1[Quantity]
    ) + 0

and once we have that we can use the two previous measures to get the amount refunded in other shops

 

RefundsInOtherStores = [RefundsFromPurchasesInStore] - [RefundsInLikeStore]

 

These are supposed to be used in the matrix visual (or similar) with Table1[Store] in the rows, just like [RefundsFromPurchasesInStore]

 

4 REPLIES 4
Highlighted
Super User
Super User

Re: Returning amount refunded from purchase in store but refund happens in either same store or anot

Hi @Caide

 

Try the following, where Table1 is the name of the table you show. It will give you the amount refunded from purchases originated in each store.

 

1. Set Table1[Store] in the rows of a matrix visual

2. Set this measure in values of the visual

 

RefundsFromPurchasesInStore =
VAR _ReceiptsList =
    CALCULATETABLE (
        DISTINCT ( Table1[Receipt] );
        Table1[Trans_type] = "Purchase"
    )
RETURN
    SUMX (
        CALCULATETABLE (
            Table1;
            _ReceiptsList;
            Table1[Trans_type] = "Refund";
            ALL ( Table1 )
        );
        Table1[Amount] * Table1[Quantity]
    )

Code formatted with   www.daxformatter.com

 

 

Caide Frequent Visitor
Frequent Visitor

Re: Returning amount refunded from purchase in store but refund happens in either same store or anot

@AlB Thank you for this!


Would it be possible to differentiate if the refund was made in the same store or in a different one? Like Store 1: 899 / Other Stores: 579

Super User
Super User

Re: Returning amount refunded from purchase in store but refund happens in either same store or anot

@Caide

 

Yeah, if you do a minor modification to the measure above, removing the ALL ( Table1 ) in the second CALCULATETABLE, you bring back the restriction to look in the same store:  

 

RefundsInLikeStore =
VAR _ReceiptsList =
    CALCULATETABLE (
        DISTINCT ( Table1[Receipt] );
        Table1[Trans_type] = "Purchase"
    )
RETURN
    SUMX (
        CALCULATETABLE (
            Table1;
            _ReceiptsList;
            Table1[Trans_type] = "Refund"
        );
        Table1[Amount] * Table1[Quantity]
    ) + 0

and once we have that we can use the two previous measures to get the amount refunded in other shops

 

RefundsInOtherStores = [RefundsFromPurchasesInStore] - [RefundsInLikeStore]

 

These are supposed to be used in the matrix visual (or similar) with Table1[Store] in the rows, just like [RefundsFromPurchasesInStore]

 

Caide Frequent Visitor
Frequent Visitor

Re: Returning amount refunded from purchase in store but refund happens in either same store or anot

@AlB

Exactly what i was looking for!

Thanks a lot once again!