Calculate The Difference Between two Fields in Two Related Fields

[Hashiru]

	Table 1
RID	Amount	Count
Elephant	500	2
Lion	600	4
Zebra	800	6

<tbody>
</tbody>

	Table 2
RID	Amount	Count
Deer	500	2
Lion	800	5
Zebra	700	5

<tbody>
</tbody>

Desired Result

RID	Amount	Count
Elephant	-500	-2
Deer	+500	+2
Lion	+200	+1
Zebra	-100	-2

<tbody>
</tbody>

RID is the related field

What formula do I use in power query or Power Pivot?

Thanks.

Note Question should be:

Calculate The Difference Between two Fields in Two Related Tables​

 

[sandy666]

Why Zebra Count is -2 ?

RID	Amt	Cnt
Deer	500​	2​
Elephant	-500​	-2​
Lion	200​	1​
Zebra	-100​	-1​

 

[Hashiru]

Thanks Sandy666 that was mistake, it is -1. Thanks.

 

[sandy666]

so you can try:
after load tables into PQ:
table1
multiply Amount and Count by -1
then do this:

[SIZE=1]let
    Source = Table.Combine({Table1, Table2}),
    #"Grouped Rows" = Table.Group(Source, {"RID"}, {{"Amount", each List.Sum([Amount]), type number}, {"Count", each List.Sum([Count]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"RID", Order.Ascending}})
in
    #"Sorted Rows"[/SIZE]