relation between table and calculation

[snifer]

	table 1					table 2
id	reference	result km			id	km
100	200	5+20=25			100	5
101	300	10+30=40			101	10
102	400	15+40=55			102	15
					200	20
					300	30
					400	40

there is a relationship between "id" columns of table 1 and table 2 as many to one, cross filter direction single

so I want to look for id and reference from table 1 in table 2 take the value "km" and show as result  the sum of them  in the column "Result km"

[v-juanli-msft]

Hi @snifer

You could create calculated columns in table1

Column = LOOKUPVALUE(Sheet2[km],Sheet2[id],Sheet1[id])

Column 2 = LOOKUPVALUE(Sheet2[km],Sheet2[id],Sheet1[reference])

Column 3 = [Column]+[Column 2]

Or create calculated columns in table2

Column = LOOKUPVALUE(Sheet1[reference],Sheet1[id],Sheet2[id])

Column 2 = CALCULATE(SUM(Sheet2[km]),FILTER(ALL(Sheet2),[id]=EARLIER(Sheet2[Column])))

Column 3 = IF(ISBLANK([Column 2]),BLANK(),[km]+[Column 2])

 

Best Regards

Maggie

[snifer]

doesn't work it gives  me this error "a table of multiple values was supplied where a single value was expected''

[v-juanli-msft · Accepted solution]

Hi @snifer

I ignore the many to one relation from table1 to table2, when i use my formula in table2, it show the same error as yours, but it is correct in the table, please refer to my first method.

Create calculated columns in table1

Column = LOOKUPVALUE(Sheet2[km],Sheet2[id],Sheet1[id])

Column 2 = LOOKUPVALUE(Sheet2[km],Sheet2[id],Sheet1[reference])

Column 3 = IF([Column 2]<>BLANK(),[Column]+[Column 2])

Best Regards

maggie

[v-juanli-msft]

Hi @snifer

Could you show me a screenshot where error occurs?

What do you do when it shows an error?

Best Regards

Maggie

[snifer]

can you look if there an easy solution?

@Zubair_Muhammad