Trying to pull column from another table

[A9kurs]

Hi Everyone,

I have two worsksheet in excel i am trying to show table in power bi.

 I need to bring Total cost from work sheet 2 into Worksheet 1 so that i can Achieve PO Issue * Total cost. I tried  Related table  it didnt work , I tried  to merge but getting error Oled db or Odbc error Please help. appreciate , thanks.

worksheet 1:

COO	ID	Vendor	Commodity	Currency	PO Issue	Pre Ship	Post Ship	Delivered
A	1	A	CcAinC	USD	0	100%	0	0
A	2	B	Arness/Actricl	USD	0	100%	0	0
A	3	C	FCriction	USD	30%	0	70%	0
A	4	D	Arness/Actricl	USD	30%	0	70%	0
A	5	E	CstC	USD	30%	70%	0	0
A	6	F	RuCCer Rolls	USD	30%	70%	0	0
A	7	G	CcAinC	USD	30%	70%	0	0
A	8	H	FCriction	USD	30%	70%	0	0
A	9	I	Arness/Actricl	USD	30%	70%	0	0
A	10	J	AcACAts	USD	50%	50%	0	0
A	11	K	AcACAts	USD	50%	50%	0	0
A	12	L	Tires	USD	50%	50%	0	0
A	13	M	Trps	USD	50%	50%	0	0
A	14	N	CstC	USD	0	0	100%	0
A	15	O	FCriction	USD	0	0	100%	0
A	16	P	FCriction	USD	0	0	100%	0

 Work Sheet 2 :

ID	Total Cost
1	$10.00
2	$11.00
3	$12.00
4	$13.00
5	$14.00
6	$15.00
7	$16.00
8	$17.00
9	$18.00
10	$19.00
11	$20.00
12	$21.00
13	$22.00
14	$23.00
15	$24.00
16	$25.00
1	$20.00
2	$21.00
3	$22.00
4	$23.00
5	$24.00
6	$25.00
7	$26.00
8	$27.00
9	$28.00
10	$29.00
11	$30.00
12	$31.00
13	$32.00
14	$33.00
15	$34.00
16	$35.00

Output :

COO	ID	Vendor	Commodity	Currency	PO Issue	Pre Ship	Post Ship	Total Cost	PO Issue * Total Cost	Pre Ship * Total Cost	Post Ship * Total Cost
A	1	A	CcAinC	USD	0	100%	0	$30.00	$0.00	$30.00	$0.00
A	2	B	Arness/Actricl	USD	0	100%	0	$32.00	$0.00	$32.00	$0.00
A	3	C	FCriction	USD	30%	0	70%	$34.00	$10.20	$0.00	$23.80
A	4	D	Arness/Actricl	USD	30%	0	70%	$36.00	$10.80	$0.00	$25.20
A	5	E	CstC	USD	30%	70%	0	$38.00	$11.40	$26.60	$0.00
A	6	F	RuCCer Rolls	USD	30%	70%	0	$40.00	$12.00	$28.00	$0.00
A	7	G	CcAinC	USD	30%	70%	0	$42.00	$12.60	$29.40	$0.00
A	8	H	FCriction	USD	30%	70%	0	$44.00	$13.20	$30.80	$0.00
A	9	I	Arness/Actricl	USD	30%	70%	0	$46.00	$13.80	$32.20	$0.00
A	10	J	AcACAts	USD	50%	50%	0	$48.00	$24.00	$24.00	$0.00
A	11	K	AcACAts	USD	50%	50%	0	$50.00	$25.00	$25.00	$0.00
A	12	L	T	USD	50%	50%	0	$52.00	$26.00	$26.00	$0.00
A	13	M	Trps	USD	50%	50%	0	$54.00	$27.00	$27.00	$0.00
A	14	N	CstC	USD	0	0	100%	$56.00	$0.00	$0.00	$56.00
A	15	O	FCriction	USD	0	0	100%	$58.00	$0.00	$0.00	$58.00
A	16	P	FCriction	USD	0	0	100%	$60.00	$0.00	$0.00	$60.00

[BrianConnelly · Accepted solution]

For Worksheet 2, create a summary table...

Summary Table = SUMMARIZE('Work Sheet 2',ID, "Total Cost",SUM([Total Cost]))

Then in Worksheet 1 use a lookup in a calculated column..

TotalCost = LOOKUPVALUE('Work Sheet 1'[ID],'Summary Table'[ID],[Total Cost])

[A9kurs]

Creted Summary Table.

But in Total cost formulae Summary table is not found and shows error.

TotalCost = LOOKUPVALUE('Work Sheet 1'[ID],'Summary Table'[ID],[Total Cost])

[BrianConnelly]

Can you share some screenshots?

[A9kurs]

Its working now Thank You so much. Appreciate your help.🙂

[BrianConnelly]

Great, could you mark my answer as solution?

[A9kurs]

Sure