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

[ARYAL_Gunjan369]

Hello Community,

I’m currently working on a dashboard in Power BI to manage and visualize the distribution of a daily production plan in a repetitive manufacturing setup. I’m facing challenges with handling a multi-level Bill of Materials (BOM) structure, particularly in distributing the production quantities from parent items down to their respective children and sub-children.

For example, I have:

• 2 Parent items (e.g., Parent A = 5000 PCS, Parent B = 3000 PCS)
• 5 Child items
• 10 Sub-child (enfant) items

I only have the production plan at the parent level, and I need to explode the BOM to calculate and distribute the required quantities to each child and sub-child based on their usage in the hierarchy.

Has anyone implemented a similar solution in Power BI? I’d appreciate any guidance, DAX patterns, or data modeling tips to achieve this.

Thanks in advance

[v-pgoloju]

Hi @ARYAL_Gunjan369,

kindly requesting you to share sample data in a workable format such as a text file, Excel file, or PBIX file.

Additionally, please include clear and detailed requirements on additional requirements, along with the expected output, so we can better understand the scenario and assist you more effectively.

Regards,

Prasanna Kumar

[v-pgoloju]

Hi @ARYAL_Gunjan369,

 we kindly request you to share sample data in a workable format such as a text file, Excel file, or PBIX file.

Additionally, please include clear and detailed requirements on additional requirements, along with the expected output, so we can better understand the scenario and assist you more effectively.

[v-pgoloju]

Hi @ARYAL_Gunjan369,

If the issue still persists, we kindly request you to share sample data in a workable format such as a text file, Excel file, or PBIX file  rather than screenshots.

Additionally, please include clear and detailed requirements, along with the expected output, so we can better understand the scenario and assist you more effectively.

Thanks & Regards,

Prasanna Kumar

[ARYAL_Gunjan369]

Sorry to say that this solution didnot worked for me in my case. As it is not related only to one hierarchy as i told in my query that the partents and child must match the profit center and mrp controller. Thanks.

[danextian · Accepted solution]

I based the solution on the sample data. It is difficult to come up with a solution when the sample data is not a complete representation of the actual. 

[v-pgoloju]

Hi @ARYAL_Gunjan369,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @danextian  for prompt and helpful responses.

Just following up to see if the solution provided by community member were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[ARYAL_Gunjan369]

Thanks for the response. Due to data security policies, I can’t share the actual PBIX or Excel files, but here’s a simplified and relatable example:

---

Sample Data Structure

BOM Table (Parent-Child Relationships):

Parent Item Child Item Quantity per Parent

Parent A	Child 1	2
Parent A	Child 2	3
Child 1	Enfant 1	1
Child 1	Enfant 2	2
Child 2	Enfant 3	1
Parent B	Child 3	1
Parent B	Child 4	2
Child 4	Enfant 4	3
Child 4	Enfant 5	1

Production Plan Table:

Parent Item Planned Quantity

Parent A	5000
Parent B	3000

---

Expected Output

I want to calculate the required quantities for each child and sub-child based on the parent production plan and the BOM structure. For example:

• Parent A = 5000 PCS

  • Child 1 = 5000 × 2 = 10,000
    • Enfant 1 = 10,000 × 1 = 10,000
    • Enfant 2 = 10,000 × 2 = 20,000
  • Child 2 = 5000 × 3 = 15,000
    • Enfant 3 = 15,000 × 1 = 15,000

• Parent B = 3000 PCS

  • Child 3 = 3000 × 1 = 3000
  • Child 4 = 3000 × 2 = 6000
    • Enfant 4 = 6000 × 3 = 18,000
    • Enfant 5 = 6000 × 1 = 6000

---

Additional Requirements

• The Parent, Child, and Enfant items are produced in different profit centers and may be managed by different MRP controllers.
• I need to implement dynamic slicers in the dashboard to filter by:
  • Profit Center
  • MRP Controller
  • Material Hierarchy (Parent → Child → Enfant)
• The goal is to visualize the distributed production plan across all levels, filtered by organizational structure and material hierarchy.

[danextian]

Hi @ARYAL_Gunjan369 

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.

[danextian]

Hi @ARYAL_Gunjan369 

Please start by  providing a workable sample data (not an image), your expected result from the same sample data and your reasoning behind -

how do you intend to explode/breakdown the parent total into into its sub-children?

weighted percentage? Equally?

You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.