Multiplication within column to aggregate BOM levels

[Anonymous]

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? 

[v-jiascu-msft · Accepted solution]

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] )
    )
)

Best Regards,
Dale

[Anonymous]

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]

Hi Blake,

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

Best Regards,
Dale