cancel
Showing results for
Did you mean:
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?

 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
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
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

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

## 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

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

## 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]

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!