Calculating Qty and price for components of sales BOMs (bill of materials)

[leightx]

Hello!

I'm trying to figure out the best way to build a model with 4 classes of SKUs:

1) Purchase SKUs that are not BOMs or components of other items. In the sales table below, item F. 

2) Purchase SKUs that are sales BOMs (a parent SKU with 1 or more child SKUs). Sales BOMs all have only 1 level - there are not parent > child > grandchild BOMs. In BOM and sales tables below, items A, B, and C. 

3) Purchase SKUs that are also component SKUs (in other words, SKU E is a component of SKU A and C, but also sold individually as E). In BOM and sales table, item E. 

4) Component SKUs that are only sold as components of sales BOMs (may not need a separate category?) - In BOM table, item D.

I've tried doing the BOM explosion, but I'm not really sure it's needed since there is really only 1 level here. Ideally, I'd like to get quantity at the "lowest" level of purchase - which is component SKUs for BOM items, and purchase SKUs for items that are not BOMs (including those components that are sold on their own). 

In an ideal world, I'd like to get the sales price at that level as well. I do have a price list, but the sales price often includes discounts and such that wouldn't be reflected in that. I guess I could calculate the % discount of the BOM and apply that to the components, then sum up?

[v-lili6-msft]

HI, @leightx 

For your case, I think the best way is create a new table that combine two tables into one table.

You may try this way:

Step1:

Use this logic to create a new table

Table = var  _salestable=FILTER(SELECTCOLUMNS(Sales,"BOM SKU",BLANK(),"SKU",[Purchase],"Qty Per Bom",[Qty],"Price of Comp SKU",0,"Ext Price Comp Sku",[Revenue],"_salesQty",[Qty]),NOT([SKU]) IN VALUES(BOM[BOM SKU])) return
var _bomtable=ADDCOLUMNS(BOM,"_salesQty",RELATED(Sales[Qty])) return
var _table=UNION(_bomtable,_salestable) return
_table

Step2:

then use a simple measure get the result:

Qty Per sales = SUMX('Table',[_salesQty]*[Qty Per Bom])

Result:

here is sample pbix file, please try it.

Best Regards,

Lin