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

[Anonymous]

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?

Receipt	Store	Trans_type	Amount	Quantity	Date
5545	1	Purchase	299	1	2018-11-20
5545	1	Purchase	499	1	2018-11-20
5545	1	Purchase	1799	1	2018-11-20
5545	1	Purchase	199	1	2018-11-20
5562	2	Purchase	199	1	2018-11-21
5514	1	Purchase	349	1	2018-11-22
5514	1	Purchase	499	1	2018-11-22
5545	5	Refund	-299	-1	2018-11-26
5545	5	Refund	-499	-1	2018-11-26
5562	2	Refund	-199	-1	2018-11-26
5514	3	Refund	-499	-1	2018-12-04

[AlB · Accepted solution]

Hi @Anonymous

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  

[AlB · Accepted solution]

Hi @Anonymous

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  

[Anonymous]

@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

[AlB · Accepted solution]

@Anonymous

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]

[Anonymous]

@AlB

Exactly what i was looking for!

Thanks a lot once again!