Forum Discussion
Help with Multi-Level BOM Explosion for Daily Production Plan Distribution in Power BI
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.
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 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 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
- Child 1 = 5000 × 2 = 10,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.
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.