BOM Modeling

[Iamnvt]

hi,

I have 3 tables:

1. Product Master data as dimension table for all material code

2. BOM: 1 Material contains many child components

3. Sales Order table: as fact table for orders of each parent material codes

--> what is the approach for calculating child components requirement from the Sales orders?

Material requirement of Purchased Item  = Requested Quantity (from Sales orders) x Usage (from BOM table)

Thanks,

[v-yuezhe-msft]

@Iamnvt,

Please create a measure in the BOMReport table using DAX below.

Material requirement of Purchased Item  = SUM ( 'BOMReport'[Usage] ) * SUMX ( RELATEDTABLE (OB), OB[Requested Quantity] )  )

And you can review the example in my environment.

Measure = SUM ( 'product'[price] ) * SUMX ( RELATEDTABLE ( sales ), sales[amount] )  )

However, if you don't get expeted result using the above DAX, please share sample data of your tables. Do mask sensitive data before uploading sample data.

Regards,
Lydia

[Iamnvt]

hi,

thanks for your answer. It seems not working.

I attached a sample with the expected result. This can be done through merge function in PQ, however, I don't want to flatten the material requirement in BOM table.

https://1drv.ms/x/s!Aps8poidQa5zku5ZWW9lT_x6em_EZA

Just want to find a DAX solution to calculate the component requirements.

Thanks again,

[v-yuezhe-msft]

@Iamnvt,

Create a new table using DAX below.

Table = GENERATEALL(BOM,var productid =BOM[Product] return SELECTCOLUMNS(CALCULATETABLE(Sales,Sales[Product]=productid),"sales",Sales[Sales]))

Then create a table  visual as follows.




Regards,
Lydia

[Iamnvt · Accepted solution]

hi,

I found the solution through Many to Many relationship topic.

https://exceleratorbi.com.au/many-many-relationships-dax-explained/#comment-23150

thanks for helping anyway.