Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

BOM explosion inside BOM

Hello, please I need some help on the following situation:

 

I have 3 tables:

 

SalesOrderTable

OrderNumProductQuantity
ON-01P110
ON-01P220
ON-01P330
ON-02P120
ON-02P215

 

ProductTable

ProductCategory
P1FinishedProduct
P2FinishedProduct
P3FinishedProduct
RM1RawMaterial
RM2RawMaterial
RM3RawMaterial

 

BillsOfMaterialTable 

FinishedProductRawMaterialPercentage%
P1RM10,33
P1RM20,33
P1P20,33
P2RM20,5
P2RM30,5

 

I need to explode the BOM Formula with SalesOrderTable products in order to measure FinishedProduct and Raw material quantities, but if we look further, inside P1 we have another finished product called P2, that needs explosion in order to get RM quantities as well, creating another iteration, and so on....

It's not that hard when we have only one iteration, but as we go with more than one, it's getting complicated and memory consuming...

So, what would be the best way to calculate all iterations without creating so many conditions and collumns in order to have all Finished Products quantities and Raw Materials? 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create measure.

    Finished Products quantities =
var _select=SELECTEDVALUE('SalesOrderTable'[Product])
var _sumquantity=SUMX(FILTER(ALL(SalesOrderTable),'SalesOrderTable'[Product]=_select),[Quantity])
var _p1=MAXX(FILTER(ALL('Product Category'),'Product Category'[Product]=_select),[Category])
var _column=SELECTCOLUMNS(FILTER(ALL('Product Category'),'Product Category'[Category]=_p1),"1",[Product])
return
IF(
    MAX('BillsOfMaterialTable'[FinishedProduct]) in _column&&MAX('BillsOfMaterialTable'[FinishedProduct])=_select,_sumquantity *MAX('BillsOfMaterialTable'[Percentage%]),0)
    Raw Materials =
var _select=SELECTEDVALUE('SalesOrderTable'[Product])
var _sumquantity=SUMX(FILTER(ALL(SalesOrderTable),'SalesOrderTable'[Product]=_select),[Quantity])
var _p1=MAXX(FILTER(ALL('Product Category'),'Product Category'[Product]=_select),[Category])
var _column=SELECTCOLUMNS(FILTER(ALL('Product Category'),'Product Category'[Category]=_p1),"1",[Product])
return
IF(
    NOT(MAX('BillsOfMaterialTable'[RawMaterial]))in _column&&MAX('BillsOfMaterialTable'[FinishedProduct])=_select,_sumquantity *MAX('BillsOfMaterialTable'[Percentage%]),0)

    2. Result:

    If the results do not meet your expectations, can you show the expected results in the form of pictures, how to obtain Finished Products quantities and Raw Materials, we can better help you

     

    Best Regards,

    Liu Yang

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create measure.

    Finished Products quantities =
var _select=SELECTEDVALUE('SalesOrderTable'[Product])
var _sumquantity=SUMX(FILTER(ALL(SalesOrderTable),'SalesOrderTable'[Product]=_select),[Quantity])
var _p1=MAXX(FILTER(ALL('Product Category'),'Product Category'[Product]=_select),[Category])
var _column=SELECTCOLUMNS(FILTER(ALL('Product Category'),'Product Category'[Category]=_p1),"1",[Product])
return
IF(
    MAX('BillsOfMaterialTable'[FinishedProduct]) in _column&&MAX('BillsOfMaterialTable'[FinishedProduct])=_select,_sumquantity *MAX('BillsOfMaterialTable'[Percentage%]),0)
    Raw Materials =
var _select=SELECTEDVALUE('SalesOrderTable'[Product])
var _sumquantity=SUMX(FILTER(ALL(SalesOrderTable),'SalesOrderTable'[Product]=_select),[Quantity])
var _p1=MAXX(FILTER(ALL('Product Category'),'Product Category'[Product]=_select),[Category])
var _column=SELECTCOLUMNS(FILTER(ALL('Product Category'),'Product Category'[Category]=_p1),"1",[Product])
return
IF(
    NOT(MAX('BillsOfMaterialTable'[RawMaterial]))in _column&&MAX('BillsOfMaterialTable'[FinishedProduct])=_select,_sumquantity *MAX('BillsOfMaterialTable'[Percentage%]),0)

    2. Result:

    If the results do not meet your expectations, can you show the expected results in the form of pictures, how to obtain Finished Products quantities and Raw Materials, we can better help you

     

    Best Regards,

    Liu Yang

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