Text lookup between two columns to sum values

[Anonymous]

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

[Anonymous]

@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. 

Power BI Test File 

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

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

[v-lionel-msft]

Hi @Anonymous ,

Or do like this. Please refer to my .pbix file.

Best regards,
Lionel Chen

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

[Anonymous]

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]

@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())

[Anonymous]

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?