Help with Multi-Level BOM Explosion for Daily Production Plan Distribution in Power BI

Hi Anonymous 

To facililate the calculation, transform the Parent-Child table into a  format where there is a separate column for the Parent, Child and Infant. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsSs0rUXBU0lFyzsjMSVEwBLKMlGJ1sEgZAVnGYCmEUte8tESgIhDTELuUEdxAhClQKWO4LqhdTnC78EiZoBlogjDQBM2FSFKmEANjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Parent Item" = _t, #"Child Item" = _t, #"Quantity per Parent" = _t]),
    // Get list of parent and child columns
    ParentList = List.Distinct(Source[Parent Item]),
    ChildList = List.Distinct(Source[Child Item]),

    // Leaf nodes = children that never appear as parents
    LeafNodes = List.Difference(ChildList, ParentList),

    // Filter only leaf node rows
    LeafsOnly = Table.SelectRows(Source, each List.Contains(LeafNodes, [Child Item])),

    // Define recursive function to trace lineage upward
    RecurseFn = 
        let
            f = (child as text) as list =>
                let
                    parentRow = Table.SelectRows(Source, each [Child Item] = child),
                    parent = if Table.IsEmpty(parentRow) then null else parentRow{0}[Parent Item]
                in
                    if parent = null then {child}
                    else List.Combine({@f(parent), {child}})
        in f,

    // Add full lineage path for each leaf
    WithPath = Table.AddColumn(LeafsOnly, "Path", each RecurseFn([Child Item])),

    // Split the path into Parent, Child, Enfant
    WithHierarchy = Table.AddColumn(WithPath, "Hierarchy", each [
        Parent = try [Path]{0} otherwise null,
        Child = try [Path]{1} otherwise null,
        Enfant = ( try [Path]{2} otherwise null ) ?? Child
    ]),
    #"Expanded Hierarchy" = Table.ExpandRecordColumn(WithHierarchy, "Hierarchy", {"Parent", "Child", "Enfant"}, {"Parent", "Child", "Enfant"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Hierarchy",{"Parent", "Child", "Enfant", "Quantity per Parent"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Quantity per Parent", "Enfant Qty"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Child Qty", each let 
child = [Child],
row = List.PositionOf(Source[Child Item], child)
in 
Source[Quantity per Parent]{row}),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Parent", "Child", "Child Qty", "Enfant", "Enfant Qty"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Parent", type text}, {"Child", type text}, {"Child Qty", Int64.Type}, {"Enfant", type text}, {"Enfant Qty", Int64.Type}})
in
    #"Changed Type"

Note: custom function above was created with the help of AI with minor editing.

Once loaded, create a one to many single direction relationship from the planned quantity table to parent child hierarchy.

Create this calculated column

Planned Qty = 
VAR _PlannedQtyByParent =
    RELATED ( PlannedQuantity[Planned Quantity] )
RETURN
    ParentChildHierarchy[Child Qty] * ParentChildHierarchy[Enfant Qty] * _PlannedQtyByParent

Please see the attached pbix.