Calculate The Difference Between two Fields in Two Related Fields

[Hashiru]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Table 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RID[/TD]
[TD]Amount[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]Elephant[/TD]
[TD]500[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Lion[/TD]
[TD]600[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Zebra[/TD]
[TD]800[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]










[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Table 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RID[/TD]
[TD]Amount[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]Deer[/TD]
[TD]500[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Lion[/TD]
[TD]800[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Zebra[/TD]
[TD]700[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]









Desired Result
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]RID[/TD]
[TD]Amount[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]Elephant[/TD]
[TD]-500[/TD]
[TD]-2[/TD]
[/TR]
[TR]
[TD]Deer[/TD]
[TD]+500[/TD]
[TD]+2[/TD]
[/TR]
[TR]
[TD]Lion[/TD]
[TD]+200[/TD]
[TD]+1[/TD]
[/TR]
[TR]
[TD]Zebra[/TD]
[TD]-100[/TD]
[TD]-2[/TD]
[/TR]
</tbody>[/TABLE]









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 ?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]RID[/td][td=bgcolor:#70AD47]Amt[/td][td=bgcolor:#70AD47]Cnt[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Deer[/td][td=bgcolor:#E2EFDA]

500​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Elephant[/td][td]

-500​

[/td][td]

-2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Lion[/td][td=bgcolor:#E2EFDA]

200​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Zebra[/td][td]

-100​

[/td][td]

-1​

[/td][/tr]
[/table]

 

[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]