BOM (Bill of materials) explosion in Power BI

[speedramps]

How to do a BOM (Bill of materials) explosion in Power BI ?

No AI replies or wild gueses please.
Only reply if you have a tested example solution.
Many thanks for your help.

Download example Bill Of Materials data from OneDrive here

Click here to download PBIX from Onedrive 

The problem:- 

A fictional vehicle manufacturer reuses the same wheel, brake and seat components in its range of bikes, cars and buses.

The DAX PATH command only supports hierarchies with one parent.  It will not support hierarchies with two or more parents.
 

@

The Part Table 

The Part table contains the costs of components parts but not any kits.

The BOM Table

The BOM table contains a list of the bill of materials. Also see the BOM diagram.

Note that some parts are used as components in multiple kits.

The report page

The report page is unfinished, awaiting your solution

Question:-

What is the best method of calculating the total cost of each vehicle and showing the breakdown of costs ?

Acceptance  criteria:-

The solution should be Power BI and not Excel.

 The solution should be able to cope with multiple depth layers of BOM (Bill Of Materials)

 and preferably use DAX rather than Power Query. Unless it is essential to explode the BOM using Power Query.

 
Please provide a PBIX example of the solution.

 When the report is refreshed the report should calculate the new total costs when any of the part costs   or BOM change on the tables.

Many thanks for your help.

Sent to :-
@danextian
@ThxAlot
@Ashish_Mathur
@mark_endicott
@AlexisOlson

[Greg_Deckler · Accepted solution]

@speedramps You're going to need to use PQ to explode the BOM because DAX's join functions are really pretty awful. Here is your query, the rest should be pretty straightforward in DAX, just some IF statements, ADDCOLUMNS, and LOOKUPVALUE or MAXX/MINX. Let me know if you need further assistance.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZA9DoMwDIWvgjIzFPpzAJg7gcSAGFJqKSgFIycIcftSICEpVScr7332s1OW7I4a6dFIYCHLgOu5RKwKfaMQAK+5xouTctrhqyMZ7LJoyaB2LD45muFui7YR6aBEg51N2eSsJhiDntfSOqY9nwgCalq7sjGyHiV8giK/ISEuIVCT0tAepnlB2wU+n2M/+08b92UnqDW2/wgv4rxe6UrrQwsCZ8YPohv0/k1He+RKAK0R1Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Kits = _t, Component = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Kits", type text}, {"Component", type text}, {"Quantity", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Kits] = "Bus" or [Kits] = "Car" or [Kits] = "Motorbike")),
    #"__Level2" = Table.SelectRows(#"Changed Type", each ([Kits] = "Seat" or [Kits] = "Wheel")),
    #"__Level3" = Table.SelectRows(#"Changed Type", each ([Kits] = "Brake system" or [Kits] = "Screw pack")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Component"}, __Level2, {"Kits"}, "Level2", JoinKind.LeftOuter),
    #"Expanded Level2" = Table.ExpandTableColumn(#"Merged Queries", "Level2", {"Kits", "Component", "Quantity"}, {"Level2.Kits", "Level2.Component", "Level2.Quantity"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Level2", {"Level2.Component"}, __Level3, {"Kits"}, "Level3", JoinKind.LeftOuter),
    #"Expanded __Level3" = Table.ExpandTableColumn(#"Merged Queries1", "Level3", {"Kits", "Component", "Quantity"}, {"Level3.Kits", "Level3.Component", "Level3.Quantity"})
in
    #"Expanded __Level3"

For example, you could add the following two DAX columns:

Level2.Cost = 
    VAR __Cost = MAXX( FILTER( 'Part', [Part] = [Level2.Component] ), [Cost] )
    VAR __Result = [Quantity] * [Level2.Quantity] * __Cost
RETURN
    __Result




Level3.Cost = 
    VAR __Cost = MAXX( FILTER( 'Part', [Part] = [Level3.Component] ), [Cost] )
    VAR __Result = [Quantity] * [Level2.Quantity] * [Level3.Quantity] * __Cost
RETURN
    __Result

[v-tsaipranay]

Hi @speedramps ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @speedramps ,

Could you please let us know the ETA by when you will be able to test the provided solution? This will help us to know when we can follow up. If you encounter any challenges during implementing the provided solution, please inform us and we’ll be glad to assist.

Thank you.

[Mr_Kerry]

I am working on the exact same problem.

Everything seems to take you in circular loops.  AI seems useless.

Some sources reccomend having a flattened BOM table or have a self joined ParentChild table  (same thing).

PowerBi could not handle 500K rows for a self joined, however there could have been circualr loops somehwere.

I ended up taking the table in SQL and flattening it into ParentKey, ChildKey1, ChildKey2....

However the next problem appears to be that a Parent can have a multipe children so if one attempts to use a calcaulted column, that fails so the next thing would be to create a calcualted table of ChildKey1 values.

I am trying to use the matrix visual but have found some visuals will do all of this for you if you want to pay for them.

[Mr_Kerry]

Solved: IsInScope() and Matrix Visual Question - Microsoft Fabric Community

This.  Progress.  

I can get the Costs in columns, probably not hard to put it in one but it looks kinda nice indented.

[v-tsaipranay]

Hi @speedramps 

Thank you for reaching out to the Microsoft fabric community forum. 

Could you please confirm if the issue has been resolved. I wanted to check if you had the opportunity to review the information provided by @ThxAlot , @AlexisOlson and @Greg_Deckler helpful for you to resolve your issue. Please feel free to contact us if you have any further questions.

Thank you.

[speedramps]

Thank you and thumbs up to @ThxAlot , @Greg_Deckler  and @AlexisOlson 

It will take me a while top read and digest this and I want to keep the post to for other replies,

but I will accept multiple solutions as soon as possible. Thank you

[ThxAlot · Accepted solution]

Lucky you reaching out to me for this subject. I delved into such type of scenario quite some ago and came up with a generic solution.

First of all, forget DAX, at least at current stage as DAX has very limited capacity to handle such questions which inevitablly involves recursion. Secondly, "BOM Explosion" is essentially a variant of "graph traversal" question; there are quite matured algorithms for it.

My solution is based on DFS algorithm; it tackles a BOM containing whatever depths.

[AlexisOlson]

Because DAX PATH functions do not support multiple parents, for a good solution, you'll almost certainly want to create a dimension table that DAX can work with, whether you do this in Power Query or with DAX calculated tables.

For this relativley simple example, it would be feasible to do a DAX calculated dimension table, but I don't think I would be generalizable to hierarchies of arbitrary depth. So the best approach is likely a general solution done upstream in Power Query (or SQL, Python, R, etc).

[Greg_Deckler · Accepted solution]

@speedramps You're going to need to use PQ to explode the BOM because DAX's join functions are really pretty awful. Here is your query, the rest should be pretty straightforward in DAX, just some IF statements, ADDCOLUMNS, and LOOKUPVALUE or MAXX/MINX. Let me know if you need further assistance.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZA9DoMwDIWvgjIzFPpzAJg7gcSAGFJqKSgFIycIcftSICEpVScr7332s1OW7I4a6dFIYCHLgOu5RKwKfaMQAK+5xouTctrhqyMZ7LJoyaB2LD45muFui7YR6aBEg51N2eSsJhiDntfSOqY9nwgCalq7sjGyHiV8giK/ISEuIVCT0tAepnlB2wU+n2M/+08b92UnqDW2/wgv4rxe6UrrQwsCZ8YPohv0/k1He+RKAK0R1Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Kits = _t, Component = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Kits", type text}, {"Component", type text}, {"Quantity", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Kits] = "Bus" or [Kits] = "Car" or [Kits] = "Motorbike")),
    #"__Level2" = Table.SelectRows(#"Changed Type", each ([Kits] = "Seat" or [Kits] = "Wheel")),
    #"__Level3" = Table.SelectRows(#"Changed Type", each ([Kits] = "Brake system" or [Kits] = "Screw pack")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Component"}, __Level2, {"Kits"}, "Level2", JoinKind.LeftOuter),
    #"Expanded Level2" = Table.ExpandTableColumn(#"Merged Queries", "Level2", {"Kits", "Component", "Quantity"}, {"Level2.Kits", "Level2.Component", "Level2.Quantity"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Level2", {"Level2.Component"}, __Level3, {"Kits"}, "Level3", JoinKind.LeftOuter),
    #"Expanded __Level3" = Table.ExpandTableColumn(#"Merged Queries1", "Level3", {"Kits", "Component", "Quantity"}, {"Level3.Kits", "Level3.Component", "Level3.Quantity"})
in
    #"Expanded __Level3"

For example, you could add the following two DAX columns:

Level2.Cost = 
    VAR __Cost = MAXX( FILTER( 'Part', [Part] = [Level2.Component] ), [Cost] )
    VAR __Result = [Quantity] * [Level2.Quantity] * __Cost
RETURN
    __Result




Level3.Cost = 
    VAR __Cost = MAXX( FILTER( 'Part', [Part] = [Level3.Component] ), [Cost] )
    VAR __Result = [Quantity] * [Level2.Quantity] * [Level3.Quantity] * __Cost
RETURN
    __Result

[speedramps]

Thank you @Greg_Deckler 

Many thanks for the Power Query copy/paste script.  It was very much appreciated !

In your example you have hard coded each level for example:-

•  L1 = Bus, Car, Motorbike
•  L2 = Seat, Wheel.
• L3 = Brake system, Screw Pack

However, in the real world we wont know the know the names.

Can you suggest a way of determining each depth ?

As previously mentioned, the PATH command will only determin depth for hierachies that have one parent.
The PATH command wont determin depth for components used in multiple kits.

Also note some components are used at different levels.

For example screwpacks are used at Level 2 in Wheels and Level 3 in Brake Systems.

Many thanks for taking time to help

[Greg_Deckler]

@speedramps I'll give it some thought but it's generally rather difficult to invent data out of thin air. You'd almost need another table that defined all the relationships and levels essentially. IDK, need to ponder it a bit.

[AlexisOlson]

You don't need to invent data; levels are based on depth. It's a solved problem in Power Query.

See here for example: Dynamically flatten Parent-Child Hierarchies in DAX and PowerBI –