cancel
Showing results for
Did you mean:
Highlighted
New Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

Re: Merge two tables while summing values from subtable

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

2 REPLIES 2
Microsoft

Re: Merge two tables while summing values from subtable

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

Highlighted
New Member

Re: Merge two tables while summing values from subtable

Perfect! Thank you.

Announcements

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors