Help needed with BOM Report

[arepnoy]

I’ve been working on this issue for a couple of days and haven’t been able to find a solution. I have a report with the following structure:

Column (1): Parent Part Number
Column (2): Subcomponent Part Number
Column (3): Quantity of Subcomponent Needed to Manufacture the Parent Part Number

Additionally, I have a separate report that provides the quantity of each Parent Part Number sold. My goal is to calculate the total quantity of subcomponents required to produce the sold parent parts. The challenge is that some parts have multiple levels of subcomponents, as illustrated in this example:

Part #1 requires:
1 of Part #2
1 of Part #3
1 of Part #4

Part #3 requires:
3 of Part #5
6 of Part #6

Part #6 requires:
10 of Part #7

What I’d like to achieve is a breakdown that, when filtered or sliced by Part #1, displays the total quantities of all subcomponents needed across all levels, like this (for qty of 1 of Part #1):

1 of Part #2
1 of Part #4
3 of Part #5
60 of Part #7

[v-achippa]

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

[arepnoy]

Hi Anjan,

Thank you for providing a solution, however it doesn't seem to work. See error below:

[v-achippa · Accepted solution]

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

[v-achippa]

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

[v-achippa]

Hi @arepnoy,

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

[v-achippa]

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

[ryan_mayu]

could you pls provide some sample data?

[arepnoy]

Here you go:

Parent P/N	BOM P/N	Qty
1	2	1
1	3	1
1	4	1
3	5	3
3	6	6
6	7	10