Forum Discussion
Merge two tables while summing values from subtable
Hello, I have two tables
Table A is the Payment Table
| Payment ID | Payment Amount | Payment Date |
| 1 | 10 | 1/1/17 |
| 2 | 10 | 1/2/17 |
| 3 | 10 | 1/3/17 |
| 4 | 10 | 1/4/17 |
| 5 | 10 | 1/5/17 |
| 6 | 10 | 1/6/17 |
Table B is the Refund Table
| Refund ID | Payment ID | Refund Amount | Refund Date |
| a | 1 | 5 | 1/1/17 |
| b | 1 | 5 | 1/2/17 |
| c | 2 | 3 | 1/3/17 |
| d | 2 | 3 | 1/4/17 |
| e | 2 | 3 | 1/5/17 |
| f | 3 | 2 | 1/6/17 |
I would like to merge them together to get Table C which is the Payment Table (Table A) with the total refunds for each payment from the Refund Table (Table B) summed up into the new Refund Amount column
Table C - Required Result
| Payment ID | Payment Amount | Payment Date | Refund Amount |
| 1 | 10 | 1/1/17 | 10 |
| 2 | 10 | 1/2/17 | 9 |
| 3 | 10 | 1/3/17 | 2 |
| 4 | 10 | 1/4/17 | 0 |
| 5 | 10 | 1/5/17 | 0 |
| 6 | 10 | 1/6/17 | 0 |
Currently when I merge these I get the following which is wrong as it creates multiple payment ID rows for each refund amount.
| Payment ID | Payment Amount | Payment Date | Refund Amount |
| 1 | 10 | 1/1/17 | 5 |
| 1 | 10 | 1/1/17 | 5 |
| 1 | 10 | 1/1/17 | 3 |
| 2 | 10 | 1/2/17 | 3 |
| 2 | 10 | 1/2/17 | 3 |
| 3 | 10 | 1/3/17 | 2 |
| 4 | 10 | 1/4/17 | 0 |
| 5 | 10 | 1/5/17 | 0 |
| 6 | 10 | 1/6/17 | 0 |
Thank you in advance for your help.
Hi jeffs9876,
According to your description above, adding a calculate column in Payment Table should be a better choice than merging the two tables in your scenario. :smileyhappy:
1. Create a relationships between the Payment table and Refund table with the Payment ID column if there isn't yet.
2. Then you should be able to use the formula below to add a new calculate column in Payment table to get the total refunds for each payment from the Refund Table.
Refund Amount = CALCULATE(SUM(Refund[Refund Amount])) + 0
Regards
2 Replies
Hi jeffs9876,
According to your description above, adding a calculate column in Payment Table should be a better choice than merging the two tables in your scenario. :smileyhappy:
1. Create a relationships between the Payment table and Refund table with the Payment ID column if there isn't yet.
2. Then you should be able to use the formula below to add a new calculate column in Payment table to get the total refunds for each payment from the Refund Table.
Refund Amount = CALCULATE(SUM(Refund[Refund Amount])) + 0
Regards
- Perfect! Thank you.
