SUM values ​​from another table with LOOKUPVALUE

[req77]

Hello,
I have two tables with this structure:
TABLE1

ID	Abstract_Weight
ID001	10
ID002	3
ID003	5

TABLE2

ID	Hot_Weight	Cold_Weight
ID001	1	3
ID001	1,5	0
ID001	2	1
ID002	5	2
ID002	4	1

I want to create columns in my TABLE1 where I can use the LOOKUPVALUE with SUM and with that, get the sum of the cold weight and hot weight values ​​for all distinct IDs. The expected result would be this:

ID	Abstract_Weight	Hot_Weight	Cold_Weight
ID001	10	4,5	4
ID002	3	9	3
ID003	5	0	0

[Ashish_Mathur]

Hi,

If you can create a Many to one relationship from Table2 to Table, then write these calculated column formulas in Table1

CW = CALCULATE(SUM(Table2[Cold_Weight]),RELATEDTABLE(Table2))

HW = CALCULATE(SUM(Table2[Hot_Weight]),RELATEDTABLE(Table2))

Hope this helps.

[v-rongtiep-msft · Accepted solution]

Hi @req77 ,

Please have a try.

Create 2 columns.

hot_Weight1 = CALCULATE(SUM('Table2'[Hot_Weight]),FILTER('Table2',[ID] =EARLIER(Table1[ID])))+0

Cold_Weight1 = CALCULATE(SUM('Table2'[Cold_Weight]),FILTER('Table2',[ID] =EARLIER(Table1[ID])))+0

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.