Merge two tables while summing values from subtable

[jeffs9876]

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.

[v-ljerr-msft · Accepted solution]

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. 

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

[jeffs9876]

Perfect! Thank you.