cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Responsive Resident
Responsive Resident

BOM Modeling

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,

 

Capture.PNG

 

1 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.

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

 

@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] )  )
1.JPG2.JPG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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,

@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.
1.JPG



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors