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