Forum Discussion
Text lookup between two columns to sum values
Hi All,
You have all been proven to be much smarter than me lately. I appreciate anyone who can help with this.. basically I have the below table which is bills of material. The first column is the product that the recipe is for. The second column is the raw material in the recipe with the cooresponding Quantity of that raw material in the recipe. Then finally I have the cost column for the raw material. Sometimes we have intermediates, which are just when we put a recipe product (column A) inside of another product as part of the recipe. Can someone tell me how I can fill in the cost of raw material ABC? Hope this made sense.
| Recipe Product | Raw Material | QTY % | Intermediate (Isolated) | Cost (of Raw Material)/lb |
| ABC | 123 | 10% | $ 1.25 | |
| ABC | 214 | 20% | $ 1.50 | |
| ABC | 564 | 70% | $ 1.30 | |
| CBE | ABC | 80% | ABC | (Need cost of all of ABC here which is $4.05) |
| CBE | 568 | 10% | $ 1.80 | |
| CBE | 659 | 10% | $ 2.10 |
Hi Anonymous ,
Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- amitchandak
Super User
Anonymous , A new column like
new column =
var _i = [Intermediate (Isolated)]
return
if([Intermediate (Isolated)] = [Product Raw], Sumx(filter(Table,[Recipe] =_1),[Cost (of Raw Material)/lb]), blank())Thanks for the quick reponse! I suppose I should have mentioned this before, my bad. What if my Cost/lb is actually a measure? I have a relationship in my model connecting two tables and a measure that gives me my cost/lb for each raw material (I know this measure/relationship between tables is working because it displays all my cost/lb correctly for all raw materials). With this new info, I probably can't create a new column refrencing a measure that brings in data from another table correct?
v-lionel-msftCommunity Support
Hi Anonymous ,
Or do like this. Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks a ton for taking the time to put this together. I think this is really close.. so all of my first 4 columns are in the same table (exactly like the below). The Cost (of raw material)/lb is actually a measure from a completely different table that has a list of Raw materials and cost/lb for each raw material, the measure takes the cost of the raw material and averages it by month (to get an average cost per month). This measure is then brought in the matrix table to give the avg cost/lb of each raw material. I will have instances in my data where there will be many different intermediates so classifying them as X may yield an incorrect answer when there are many (not just one), does that make sense?
Recipe Product Raw Material QTY % Intermediate (Isolated) Cost (of Raw Material)/lb ABC 123 10% $ 1.25 ABC 214 20% $ 1.50 ABC 564 70% $ 1.30 CBE ABC 80% ABC (Need cost of all of ABC here which is $4.05) CBE 568 10% $ 1.80 CBE 659 10% $ 2.10
amitchandak v-lionel-msft parry2k
I have attached a test file which shows exactly what I am trying to accomplish. Can any of you help? Again, I really appreciate your time. Below is a snip of what is in my test file.
- v-lionel-msft
Hi Anonymous ,
Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
