Forum Discussion

Iamnvt's avatar
Iamnvt
Continued Contributor
8 years ago
Solved

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 pa...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

 

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's avatar
Iamnvt
Continued Contributor
8 years ago

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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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