Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Multiplication within column to aggregate BOM levels

Hello,

 

I have an exploded BOM using ImkeF's method of conversion from a flat table, but now am having a dilemma in a parent-child conversion problem.

 

 

 

  For each item in 'Exploded_BOM'[BW_ParentItem], there is a component item in 'Exploded_BOM'[Component], corresponding BOM level in 'Exploded_BOM'[Level], and component quantity (UOM of component per 1 UOM of Item Number) in 'Exploded_BOM'[Backflush Qty] at each BOM level.

 

What I am trying to accomplish is for each parent item, multiply the quantity of the associated [Backflush Qty] at each level of the BOM > level 1 and store the values in a new column.

 

Using one item as an example:


 Here is the desired result of the new column: New Column

 

 

 Is there a way this can be done with the PRODUCT() function? 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    Please try this formula as a calculated column. 

    Column =
CALCULATE (
    PRODUCTX (
        SUMMARIZE ( 'Table1', 'Table1'[BW_Parentltem], Table1[Level], [Backflush Qty] ),
        [Backflush Qty]
    ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[BW_Parentltem] ),
        'Table1'[Level] <= EARLIER ( Table1[Level] )
    )
)

    Multiplication-within-column-to-aggregate-BOM-levels

     

    Best Regards,
    Dale

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

     

    Please try this formula as a calculated column. 

    Column =
CALCULATE (
    PRODUCTX (
        SUMMARIZE ( 'Table1', 'Table1'[BW_Parentltem], Table1[Level], [Backflush Qty] ),
        [Backflush Qty]
    ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[BW_Parentltem] ),
        'Table1'[Level] <= EARLIER ( Table1[Level] )
    )
)

    Multiplication-within-column-to-aggregate-BOM-levels

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-jiascu-msft,

      Thank you again for posting this solution - this solved my immediate problem. Because of the complexity of the BOM, I've run up against another challenge - if there are multiple branches at level 1, level 2, or level 3 of the BOM, the PRODUCTX does not correctly aggregate. Here's an example below:

       



      Item #101546 has 3 level 1 children - 25% OLEO, PM6MR, and HFE. HFE and 25% OLEO have no children of their own so the calculation would stop there; however, PM6MR has one more child at level 3, XRMD.

      The formula you provided is shown in BW_Test1 as a calculated column. 

      BW_Test1 =
CALCULATE (
    PRODUCTX (
        SUMMARIZE (
            'Exploded BOM',
            'Exploded BOM'[TopParentProduct],
            'Exploded BOM'[Level],
            'Exploded BOM'[Backflush Qty]
        ),
        'Exploded BOM'[Backflush Qty]
    ),
    FILTER (
        ALLEXCEPT ( 'Exploded BOM', 'Exploded BOM'[TopParentProduct] ),
        'Exploded BOM'[Level] <= EARLIER ( 'Exploded BOM'[Level] )
    )
)

       The answer showing for BW_Test1 for Item Number HFE should be 0.00235840 and 0.02538 for 25% OLEO, while the answer for PM6MR should be 0.004913568 (0.02538 multiplied by 0.19360).

      I created columns of each item in the BOM heirarchy as a way to further group the items (BIL1-BIL4), and by doing this with modification to the ALLEXCEPT filter, it seems to work for this example by this modification.

       

      BW_Test1 =
CALCULATE (
    PRODUCTX (
        SUMMARIZE (
            'Exploded BOM',
            'Exploded BOM'[TopParentProduct],
            'Exploded BOM'[Level],
            'Exploded BOM'[Backflush Qty]
        ),
        'Exploded BOM'[Backflush Qty]
    ),
    FILTER (
        ALLEXCEPT (
            'Exploded BOM',
            'Exploded BOM'[TopParentProduct],
            'Exploded BOM'[BIL1]
        ),
        'Exploded BOM'[Level] <= EARLIER ( 'Exploded BOM'[Level] )
    )
)

      This then generates the correct results:

       

       

      However, now I run up against another issue with the parent split on an item like the following where there is a multiple child split at level 2 in the BOM:

       

       

      Adding BIL2, BIL3, BIL4 etc to the filter does not achieve the results I'm looking for. Do you have any ideas on how I can use the BIL1/2/3/4 groupings to apply the PRODUCTX function to account for these multiple child situations?

      Thank you again!

      Blake

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Blake,

         

        I would suggest you create a new post in this forum. 

         

        Best Regards,
        Dale