Use a Sales table and BOL table to get a Total Ingredients Used report

[tbmac61]

I'm looking for help trying to combine two tables I created in Power Query.
The first table is a simple sales table telling me how many of each product sold
The second table is a BOL table telling me how much raw ingredients it takes to make each finished product
I'm looking for a way to create a report to tell me how much of each individual raw ingredients it took to make the total sales for a given date range.
I'm trying to forecast how much ingredients I have to purchase for next year based on previous years sales
I've attached a spreadsheet showing the tables I have to work with and at the bottom, the expected result
Thank you for any guidance
Steve

[josephteh]

The easiest way to do is to extend the Sales Table with 4 columns for the raw materials (RAW1 to RAW4), put in the following formula in cell F15 and copy down and across then add the Total Row for the Sales Table.

Formula:=SUMPRODUCT((Table1[MFGPROD]=[@MFGPROD])*(Table1[DETAILID]=F$14)*Table1[QTY]*[@SOLD])