Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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])
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])
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])
Can you share some screenshots?
Its working now Thank You so much. Appreciate your help.🙂
Great, could you mark my answer as solution?
Sure
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |