Forum Discussion
Help needed with BOM Report
Hi arepnoy,
Since the DAX approach is getting some circular dependency issues, use power query. Please follow below steps:
- Load the BOM table into power bi and click Transform Data to open Power Query.
- In power query duplicate the BOM table and rename it as like ExpandedBOM.
- Select the original BOM table and click on Merge Queries and merge it with ExpandedBOM like match BOM[Subcomponent Part Number] with ExpandedBOM[Parent Part Number] and use Left Outer Join
- After merging, click the expand icon in the merged column and select ExpandedBOM[Subcomponent Part Number], ExpandedBOM[Quantity per Parent] and rename these to Level2 Subcomponent, Level2 Quantity.
- To multiply quantities, add a Custom Column, this gives the total quantity of level 2 components needed.
[Quantity per Parent] * [Level2 Quantity] - Repeat the merge and expand steps using the latest subcomponent column each time, multiplying quantities at each step. Repeat until all BOM levels are covered.
- Once done, click on Close & Apply.
- In the power bi just use a simple measure like below
Total Subcomponent Qty = SUM('Flattened BOM'[Final Quantity]) - Now in a table or matrix visual, slice by any Parent Part Number, and we can see the total quantities of each subcomponent is needed across all levels.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi arepnoy,
Thank you for reaching out to Microsoft Fabric Community.
I likely understand that you need to compute the total subcomponent quantities across multiple BOM levels.
- For this load the BOM data into power bi with columns like for example Parent Part Number, Subcomponent Part Number and Quantity per Parent
- Create a Recursive Relationship i.e self-referencing relationship, the table should be linked with itself like link Subcomponent Part Number to Parent Part Number.
- Use this below DAX measure to calculate the total required subcomponent quantities dynamically:
Total Subcomponent Qty = VAR SelectedPart = SELECTEDVALUE(BOM[Parent Part Number])
RETURN SUMX(FILTER(BOM, BOM[Parent Part Number] = SelectedPart),
BOM[Quantity per Parent] *
IF(COUNTROWS(FILTER(BOM, BOM[Parent Part Number] = BOM[Subcomponent Part Number])) > 0,
[Total Subcomponent Qty], 1)) -
Create a table visual and apply filters on Parent Part Number to see the total subcomponent quantities for any selected parent part.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi Anjan,
Thank you for providing a solution, however it doesn't seem to work. See error below:
Hi arepnoy,
Since the DAX approach is getting some circular dependency issues, use power query. Please follow below steps:
- Load the BOM table into power bi and click Transform Data to open Power Query.
- In power query duplicate the BOM table and rename it as like ExpandedBOM.
- Select the original BOM table and click on Merge Queries and merge it with ExpandedBOM like match BOM[Subcomponent Part Number] with ExpandedBOM[Parent Part Number] and use Left Outer Join
- After merging, click the expand icon in the merged column and select ExpandedBOM[Subcomponent Part Number], ExpandedBOM[Quantity per Parent] and rename these to Level2 Subcomponent, Level2 Quantity.
- To multiply quantities, add a Custom Column, this gives the total quantity of level 2 components needed.
[Quantity per Parent] * [Level2 Quantity] - Repeat the merge and expand steps using the latest subcomponent column each time, multiplying quantities at each step. Repeat until all BOM levels are covered.
- Once done, click on Close & Apply.
- In the power bi just use a simple measure like below
Total Subcomponent Qty = SUM('Flattened BOM'[Final Quantity]) - Now in a table or matrix visual, slice by any Parent Part Number, and we can see the total quantities of each subcomponent is needed across all levels.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi arepnoy,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Thanks and regards,
Anjan Kumar Chippa