Power BI: BOM explosion
In this article, I will go through BOM explosion in Power BI. BOM is an acronym for Bill Of Material, the list of components included in a product.
For example, a perfume box including a full-size perfume and a sample size perfume will have these two products listed in its BOM.
'BOM explosion' is the calculation of the number of components corresponding to a certain number of products.
BOM explosion in Power BI is useful to analyze sales and to forecast the number of components needed to produce.
We will start with two tables in Power BI. The first one is a sales table, with units sold for each product.
The second table shows the components for each BOM. For example, the product 1001 is made of 1 component 2001, 2 components 2002 and finally 1 2003.
Create a new Power BI table for the BOM explosion
The BOM explosion will be in a new calculated table. This table will be similar to the sales table, but with the components instead of the products.
To create a new calculated table, click on Modeling and then New Table.
When the textbox appears, enter the DAX formula below:
Sales exploded = DISTINCT(
UNION(
VALUES(Sales[Product])
, VALUES('BOM Detail'[Component])
)
)
This formula creates one row for each distinct product, one row for each distinct component and then merges them. Below is what the table looks like.
This table includes BOM products (such as 1001) and non-BOM products (such as 2010).
Add a column with the BOM units exploded
Now we need to create a column with the sales, taking into account BOM explosion. The logic is the following.
If the product is a BOM, then units sold are zero. Units sold are split in the component products, and are not on the BOM itself
Otherwise, if the product is not a BOM (cannot be split), units are equal to the units sold of the product by itself, plus the units sold via BOM.
This leaves three variables to calculated: a variable to check if the product is a BOM, a variable with the sales of the product itself, and finally a variable with the sales of the product via BOM.
Below is the finished DAX formula, which you can paste in a new calculated column.
Units sold =
VAR currentProduct = [Product]
VAR productIsBOM=
NOT COUNTX(
FILTER('BOM Detail',
[BOM]=currentProduct
),[BOM])
= BLANK()
VAR productSales = LOOKUPVALUE(Sales[Units sold],Sales[Product],[Product],0)
VAR componentSalesViaBOM = SUMX(
'BOM Detail',
IF([Component]=currentProduct,
[Quantity per BOM]*LOOKUPVALUE(Sales[Units sold],Sales[Product],[BOM],0)
)
)
RETURN IF(productIsBOM, 0, productSales+componentSalesViaBOM)
The variable 'productIsBOM' checks if the product is a BOM.
It does that by counting the rows of the BOM table matching the current product. If there are no rows matching the current product, then the count is returns empty (blank). This means that the product is not a BOM.
The next variable productSales looks for the sales of the current product in the Sales table.
The final variable componentSalesViaBOM calculates the sales of components sold via BOM. It looks at the BOM table: for each row, it multiplies the BOM sales by the quantity of component per BOM.
And now we have a final table with the BOM explosion.
The final table has 30 units of product 2001: 20 units are coming from the direct sale of it, and 10 units are coming from the sale of BOM 1001.
I hope this is helpful!
Enjoyed the article? Subscribe to the newsletter and download the Power BI of the article for free.
5 Comments
I've just tried this example, and although it works for simple BOMs, it doesn't work for more complex BOMs.
I am looking for something similar that can deal with a BOM within a BOM within a BOM. I believe in SQL this would be a 'recursive query'.
For example I has a sub assembly of a Seal Plug. It is a Plug with a rubber oring on so it seals when assembled. It is made up of 2 parts – the Plug and the oring. It is used in many different assemblies and sub assemblies, as well as being sold on its own. This formula doesn't count the orings when the seal plug (parent assemby) is sat inside another assembly.
My challenge is that if I listed all my BOMs out on paper it wouldn't look like a tree, it would be more like a messy spiders web! Any help with this would be appreciated.
My example below if you follow it, Child P2005 resides in A1000, A1001, A1002, A1004 and A1006 because although it's parent is A1002, A1002 resides in other kits, so it essentially has grand parents and great grandparents.
Parent Child Qty per kit
A1000 P2000 1
A1000 A1001 2
A1000 P2001 1
A1001 A1002 5
A1001 P2002 2
A1001 P2003 1
A1002 P2004 1
A1002 P2005 2
A1002 P2006 3
A1003 P2006 1
A1004 A1002 1
A1005 P2000 1
A1006 A1000 1
A1006 A1002 10
A1006 P2222 1
Parent Child
A1000 P2000
A1000 A1001
A1000 P2001
A1001 A1002
A1001 P2002
A1001 P2003
A1002 P2004
A1002 P2005
A1002 P2006
A1003 P2006
A1004 A1002
A1005 P2000
A1006 A1000
A1006 A1002
A1006 P2222
Thank you Kevin for your message.
I understand your issue, and indeed this example only works for simple (one-level) BOMs.
There are some DAX functions for handling more advanced multi-levels parent-child relationships (you can take a look at https://docs.microsoft.com/en-us/dax/parent-and-child-functions-dax).
I would however recommend handling this in Power Query, before data is loaded to Power BI. Here you have multiple options:
1) You mention SQL. If your source data is SQL then you can write a custom SQL query to do the transformation and then load the transformed data to Power Query.
2) Do the transformation in Power Query. Power Query has a function for loops, which is List.Generate. I have used List.Generate in the past, it works but it takes a bit of time to wrap your head around it.
3) Use Python! You can integrate a Python script directly in Power Query.
Out of the three options, probably SQL is the one that will have a better performance but it's working checking this.
Hope this helps.
If you want more help for your specific case, you can also contact me via my email at lc@finance-bi.com
Luca
We did an iterative PBIX Query to build a 5-level Exploded BOM for an Exploded forecast dashboard for a client using Microsoft Dynamics NAV:
Basically:
Start at the second-deepest level of the BOM (Low Level Code LLC = 3)
Merge with the LLC = 4 to expand the BOM for items which are themselves BOMs.
Eliminate all non-BOM rows
Rename the pulled in fields as the primary fields
Add the original LLC = 3 to the bottom to keep all levels of the BOM (including the original items that themselves are BOM items).
Go up one level of the BOM (LLC=2)
Merge with the result of the previous step (LLC = 34) to expand the BOM for items which are themselves BOMs.
Eliminate all non-BOM rows
Rename the pulled in fields as the primary fields
Add the original LLC = 2 to the bottom to keep all levels of the BOM (including the original items that themselves are BOM items).
Go up one level of the BOM (LLC=1)
Merge with the result of the previous step (LLC = 234) to expand the BOM for items which are themselves BOMs.
Eliminate all non-BOM rows
Rename the pulled in fields as the primary fields
Add the original LLC = 1 to the bottom to keep all levels of the BOM (including the original items that themselves are BOM items).
Go to top level of the BOM (LLC=0)
Merge with the result of the previous step (LLC = 1234) to expand the BOM for items which are themselves BOMs.
Eliminate all non-BOM rows
Rename the pulled in fields as the primary fields
Add the original LLC = 0 to the bottom to keep all levels of the BOM (including the original items that themselves are BOM items).
Bam! (Boom!) Exploded BOM.
I'm sure a more elegant solution is possible, but this works.
AquilaBI.com
Thank you Bruce for your message!
I am sure that this is very useful for the readers of the blog.
The main issue I found with the DAX version is performance. How is the performance of the PBIX query?