Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aferreira_adm
Regular Visitor

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? 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @aferreira_adm ,

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:

vyangliumsft_0-1666750403627.png

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

View solution in original post

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

Hi  @aferreira_adm ,

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:

vyangliumsft_0-1666750403627.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.