Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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] )
@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]
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] )
@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
@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]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |