Calculating costs of Bill of Materials (BoM)

[Paras_Sood]

Hi Community!

I am working on a bill of materials app to calculate our costs of production.

I have two tables, Products and Recipe:

Products schema:

Products table lists all products include Raw Materials, Finished Product and Work In Progress. Each Raw Material has a cost associated to it which I want to manually enter in Cost per kg for RAW MATERIALS ONLY.

BoM Cost represents the cost to company to produce that product based on it's bill of materials. A bill of materials might be made up of raw materials ONLY or it might be made up of a WIP (with it's own Bill of materials) and a raw material. I want to calculate BoM cost for all levels of BoM. After I have calculated the BoM Cost, I want to calculate cost per kg by dividing the BoM Cost by Batch Size.

Recipe Schema:

In recipe table, I have recipe SKU and product SKU to create a link between products in the products list. I enter the quantity of each ingredient required to make a Finish product or Work In Progress Item.

My goal is to use formulas to calculate BoM Cost (For all products with ingredients > 0) and populate cost per kg (manually for raw materials and calculated for WIPs and Finish Products)

How can I do this?

I tried the following to calculate BoM Cost (because it has similarity to the order and order details approach):

SUM(
  SELECT(
    Products[Cost per kg],
    [SKU] = Recipe[Product SKU]
  )
)

But of course this is a simple SQL statement. I believe there needs to be another select statement to get all products linked to a single recipe but I can't wrap my head around how to do it.

Any help appreciated! @TeeSee1