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 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,

 

 

Need Help

4 Replies

  • 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