topic Merge two tables while summing values from subtable in Desktop
https://community.powerbi.com/t5/Desktop/Merge-two-tables-while-summing-values-from-subtable/m-p/201112#M88471
<P>Hello, I have two tables</P><P> </P><P>Table A is the Payment Table</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Payment ID</TD><TD>Payment Amount</TD><TD>Payment Date</TD></TR><TR><TD>1</TD><TD>10</TD><TD>1/1/17</TD></TR><TR><TD>2</TD><TD>10</TD><TD>1/2/17</TD></TR><TR><TD>3</TD><TD>10</TD><TD>1/3/17</TD></TR><TR><TD>4</TD><TD>10</TD><TD>1/4/17</TD></TR><TR><TD>5</TD><TD>10</TD><TD>1/5/17</TD></TR><TR><TD>6</TD><TD>10</TD><TD>1/6/17</TD></TR></TBODY></TABLE><P> </P><P> </P><P>Table B is the Refund Table</P><TABLE><TBODY><TR><TD>Refund ID</TD><TD>Payment ID</TD><TD>Refund Amount</TD><TD>Refund Date</TD></TR><TR><TD>a</TD><TD>1</TD><TD>5</TD><TD>1/1/17</TD></TR><TR><TD>b</TD><TD>1</TD><TD>5</TD><TD>1/2/17</TD></TR><TR><TD>c</TD><TD>2</TD><TD>3</TD><TD>1/3/17</TD></TR><TR><TD>d</TD><TD>2</TD><TD>3</TD><TD>1/4/17</TD></TR><TR><TD>e</TD><TD>2</TD><TD>3</TD><TD>1/5/17</TD></TR><TR><TD>f</TD><TD>3</TD><TD>2</TD><TD>1/6/17</TD></TR></TBODY></TABLE><P> </P><P>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 </P><P> </P><P>Table C - Required Result</P><TABLE><TBODY><TR><TD>Payment ID</TD><TD>Payment Amount</TD><TD>Payment Date</TD><TD>Refund Amount</TD></TR><TR><TD>1</TD><TD>10</TD><TD>1/1/17</TD><TD>10</TD></TR><TR><TD>2</TD><TD>10</TD><TD>1/2/17</TD><TD>9</TD></TR><TR><TD>3</TD><TD>10</TD><TD>1/3/17</TD><TD>2</TD></TR><TR><TD>4</TD><TD>10</TD><TD>1/4/17</TD><TD>0</TD></TR><TR><TD>5</TD><TD>10</TD><TD>1/5/17</TD><TD>0</TD></TR><TR><TD>6</TD><TD>10</TD><TD>1/6/17</TD><TD>0</TD></TR></TBODY></TABLE><P> </P><P>Currently when I merge these I get the following which is wrong as it creates multiple payment ID rows for each refund amount. </P><TABLE><TBODY><TR><TD>Payment ID</TD><TD>Payment Amount</TD><TD>Payment Date</TD><TD>Refund Amount</TD></TR><TR><TD>1</TD><TD>10</TD><TD>1/1/17</TD><TD>5</TD></TR><TR><TD>1</TD><TD>10</TD><TD>1/1/17</TD><TD>5</TD></TR><TR><TD>1</TD><TD>10</TD><TD>1/1/17</TD><TD>3</TD></TR><TR><TD>2</TD><TD>10</TD><TD>1/2/17</TD><TD>3</TD></TR><TR><TD>2</TD><TD>10</TD><TD>1/2/17</TD><TD>3</TD></TR><TR><TD>3</TD><TD>10</TD><TD>1/3/17</TD><TD>2</TD></TR><TR><TD>4</TD><TD>10</TD><TD>1/4/17</TD><TD>0</TD></TR><TR><TD>5</TD><TD>10</TD><TD>1/5/17</TD><TD>0</TD></TR><TR><TD>6</TD><TD>10</TD><TD>1/6/17</TD><TD>0</TD></TR></TBODY></TABLE><P> </P><P>Thank you in advance for your help.</P>Mon, 26 Jun 2017 08:41:16 GMTjeffs98762017-06-26T08:41:16ZMerge two tables while summing values from subtable
https://community.powerbi.com/t5/Desktop/Merge-two-tables-while-summing-values-from-subtable/m-p/201112#M88471
<P>Hello, I have two tables</P><P> </P><P>Table A is the Payment Table</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Payment ID</TD><TD>Payment Amount</TD><TD>Payment Date</TD></TR><TR><TD>1</TD><TD>10</TD><TD>1/1/17</TD></TR><TR><TD>2</TD><TD>10</TD><TD>1/2/17</TD></TR><TR><TD>3</TD><TD>10</TD><TD>1/3/17</TD></TR><TR><TD>4</TD><TD>10</TD><TD>1/4/17</TD></TR><TR><TD>5</TD><TD>10</TD><TD>1/5/17</TD></TR><TR><TD>6</TD><TD>10</TD><TD>1/6/17</TD></TR></TBODY></TABLE><P> </P><P> </P><P>Table B is the Refund Table</P><TABLE><TBODY><TR><TD>Refund ID</TD><TD>Payment ID</TD><TD>Refund Amount</TD><TD>Refund Date</TD></TR><TR><TD>a</TD><TD>1</TD><TD>5</TD><TD>1/1/17</TD></TR><TR><TD>b</TD><TD>1</TD><TD>5</TD><TD>1/2/17</TD></TR><TR><TD>c</TD><TD>2</TD><TD>3</TD><TD>1/3/17</TD></TR><TR><TD>d</TD><TD>2</TD><TD>3</TD><TD>1/4/17</TD></TR><TR><TD>e</TD><TD>2</TD><TD>3</TD><TD>1/5/17</TD></TR><TR><TD>f</TD><TD>3</TD><TD>2</TD><TD>1/6/17</TD></TR></TBODY></TABLE><P> </P><P>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 </P><P> </P><P>Table C - Required Result</P><TABLE><TBODY><TR><TD>Payment ID</TD><TD>Payment Amount</TD><TD>Payment Date</TD><TD>Refund Amount</TD></TR><TR><TD>1</TD><TD>10</TD><TD>1/1/17</TD><TD>10</TD></TR><TR><TD>2</TD><TD>10</TD><TD>1/2/17</TD><TD>9</TD></TR><TR><TD>3</TD><TD>10</TD><TD>1/3/17</TD><TD>2</TD></TR><TR><TD>4</TD><TD>10</TD><TD>1/4/17</TD><TD>0</TD></TR><TR><TD>5</TD><TD>10</TD><TD>1/5/17</TD><TD>0</TD></TR><TR><TD>6</TD><TD>10</TD><TD>1/6/17</TD><TD>0</TD></TR></TBODY></TABLE><P> </P><P>Currently when I merge these I get the following which is wrong as it creates multiple payment ID rows for each refund amount. </P><TABLE><TBODY><TR><TD>Payment ID</TD><TD>Payment Amount</TD><TD>Payment Date</TD><TD>Refund Amount</TD></TR><TR><TD>1</TD><TD>10</TD><TD>1/1/17</TD><TD>5</TD></TR><TR><TD>1</TD><TD>10</TD><TD>1/1/17</TD><TD>5</TD></TR><TR><TD>1</TD><TD>10</TD><TD>1/1/17</TD><TD>3</TD></TR><TR><TD>2</TD><TD>10</TD><TD>1/2/17</TD><TD>3</TD></TR><TR><TD>2</TD><TD>10</TD><TD>1/2/17</TD><TD>3</TD></TR><TR><TD>3</TD><TD>10</TD><TD>1/3/17</TD><TD>2</TD></TR><TR><TD>4</TD><TD>10</TD><TD>1/4/17</TD><TD>0</TD></TR><TR><TD>5</TD><TD>10</TD><TD>1/5/17</TD><TD>0</TD></TR><TR><TD>6</TD><TD>10</TD><TD>1/6/17</TD><TD>0</TD></TR></TBODY></TABLE><P> </P><P>Thank you in advance for your help.</P>Mon, 26 Jun 2017 08:41:16 GMThttps://community.powerbi.com/t5/Desktop/Merge-two-tables-while-summing-values-from-subtable/m-p/201112#M88471jeffs98762017-06-26T08:41:16ZRe: Merge two tables while summing values from subtable
https://community.powerbi.com/t5/Desktop/Merge-two-tables-while-summing-values-from-subtable/m-p/201742#M88784
<P>Hi <LI-USER uid="34092"></LI-USER>,</P>
<P> </P>
<P>According to your description above, adding a calculate <STRONG>column</STRONG> in <SPAN><STRONG>Payment</STRONG> Table should be a better choice than merging the two tables in your scenario. <img id="smileyhappy" class="emoticon emoticon-smileyhappy" src="https://community.powerbi.com/i/smilies/16x16_smiley-happy.png" alt="Smiley Happy" title="Smiley Happy" /></SPAN></P>
<P> </P>
<P><SPAN>1. Create a relationships between the Payment table and Refund table with the <STRONG>Payment ID</STRONG> column if there isn't yet.</SPAN></P>
<P> </P>
<P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="relationship1.PNG" style="width: 600px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/47612iE95752A5D096A4AF/image-size/large?v=1.0&px=999" title="relationship1.PNG" alt="relationship1.PNG" /></span></SPAN></P>
<P> </P>
<P><SPAN>2. Then you should be able to use the formula below to add a new calculate <STRONG>column</STRONG> in <STRONG>Payment</STRONG> table to get the total refunds for each payment from the Refund Table.</SPAN></P>
<PRE>Refund Amount = CALCULATE(SUM(Refund[Refund Amount])) + 0</PRE>
<P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="c2.PNG" style="width: 527px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/47613iE1856CE7DC088EA5/image-size/large?v=1.0&px=999" title="c2.PNG" alt="c2.PNG" /></span></SPAN></P>
<P> </P>
<P>Regards</P>Tue, 27 Jun 2017 02:45:40 GMThttps://community.powerbi.com/t5/Desktop/Merge-two-tables-while-summing-values-from-subtable/m-p/201742#M88784v-ljerr-msft2017-06-27T02:45:40ZRe: Merge two tables while summing values from subtable
https://community.powerbi.com/t5/Desktop/Merge-two-tables-while-summing-values-from-subtable/m-p/201784#M88795
Perfect! Thank you.Tue, 27 Jun 2017 04:20:45 GMThttps://community.powerbi.com/t5/Desktop/Merge-two-tables-while-summing-values-from-subtable/m-p/201784#M88795jeffs98762017-06-27T04:20:45Z