Multiply depending on level to find Total Qty in Bill of Materials
Hi all
I have a problem that i cannot seem to wrap my head around.
When we extract a Bill of Material from our PLM software to Excel, it is only able to tell us the quantity of the specific item - hence if the item is part of a sub-assembly of which we need 3, then it cannot tell the 'total quanntity'.
I have included an example, which also contains an example of the values needed.
Thanks in advance!
Br, Mike
Last edited by MikeOlsen8000; 03-17-2020 at 07:08 AM.
I see that you need 1 Main Assembly. Are the ratios in column C correct? If so your numbers in red are not consistent for BOM.
Typically the Main Assembly is where the multiplier is found. Please check your example.
Hi RChad, thanks for your reply.
I am trying to create a dynamic function, which can be used for all of our BoM extracts.
What I need to know is how many of each part, in total, that we will need for each Main Assembly. Because of this the multiplier should not be found in the Main Assembly, but as a continious multiplication of each level until the part.
Example:
1 x Main Assembly * 2 x Assembly 1 * 2 x Sub Assembly 1 * 2 x Part 1
Based on the above we would need a total of 8 'Part 1'.
Does that make any sense?
Br, Mi
I work in production and for our system the quantities for one main assembly is setup manually for the sub assemblies and parts needed. Then if more than one main assembly is ordered then the ratios all get multiplied by the number ordered.
Again they are manually set up initially. I am sorry but this isn't making sense to me. Perhaps someone else can help you or you can refer to a BOM template of sort.
Is there a limit to the BOM depth (in this case, 4), or is this a variable?
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Hi Olly,
Sorry, forgot to mention.
This is variable, but at the moment 12 levels is the max. However, this could easily change so if its possible to make it truly variable it would be preferred.
Br. Mike
What is BOM pray?
Including initialisms or use of professional jargon in titles is not a good idea.
Cheers
Point taken.
Thank you for your understanding
Hi Olly,
Sorry, forgot to mention.
This is variable, but at the moment 12 levels is the max. However, this could easily change so if its possible to make it truly variable it would be preferred.
Br. Mike
We can use Power Query for this.
Rather than completely reinventing the wheel, Imke Feldman has done some excellent work on processing multi-level BOMs, here: https://www.thebiccountant.com/2017/...l-and-powerbi/
In your case though, we first need to identify the Parent / Child relationships for your BOM.
First, format your source data as a table.
Now, first query:
Now we can lightly modify the query from Imke, in the link above:
Please Login or Register to view this content.
This gives you rather more than just the Qty per component, which you initially required... Have a read of Imke's article to understand further.Please Login or Register to view this content.
The attached workbook gives a simple output which matches your requirement.
Please try at D3
=IFERROR(LOOKUP(2,1/($A$2:A2=A3-1),D$2:D2),1)*C3
Thank you all for the help, Olly's solution solved the problem perfectly.
Bo_Ry, unfortunately your solution returned wrong results - but thanks for the effort.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks