cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hk2018086 Regular Visitor
Regular Visitor

Bill of Material Totals

 

I have a table in this format and need to find totals usage of all parts. please help.

 

Level Part Qty

1A1

2B5

3C3

4D2

2E1

3F1

 

 Please note that is related to the hierarchical totaling For example the total qty required for Part C, which is at Level 3 and falls under Level 2 of Part B and level 1 of Part A, would be Part B qty X Part A Qty. 

 

For Part D the total will = Part D (2)  x Part C (3) X Part B (5) x Part A (1) = 30

 

Lower levels are parents of higher levels

 

In the example below Bearing Ball = Bearing Ball (10) x BB Bearing Ball (10) x HL Bottom Bracket (1) x Mountain 100 Black 38 (1) = 100

 

Another example is shown below.

 

1BoMExplosion.jpg2aBomOrderList.jpg

24 REPLIES 24
Community Support Team
Community Support Team

Re: Bill of Material Totals

Hi @hk2018086,

 

You can do it easily by dragging the QTY column into a Card and selecting SUM. Please refer to the snapshot below.

Bill_of_Material_Totals

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hk2018086 Regular Visitor
Regular Visitor

Re: Bill of Material Totals

Just clarified the question, please check it now.

Community Support Team
Community Support Team

Re: Bill of Material Totals

Hi @hk2018086,

 

Please make it more clear. 

1. How can we calculate the other levels? For example, level 4.

2. Since there are so many 1s in the image, the equation isn't clear either. The [total qty of C] is [qty of B] * [qty of A]. So [Bearing Ball] = [BB Bearing Ball] * [HL Bottom Bracket] = 1 * 10 = 10, which isn't 100. 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hk2018086 Regular Visitor
Regular Visitor

Re: Bill of Material Totals

hi Dale

updated the question to make it clear.

Highlighted
Community Support Team
Community Support Team

Re: Bill of Material Totals

Hi @hk2018086,

 

The function product-function-dax would make this requirement easy. But there is still a difficult. It's hard to identify which one the parent level. Can you share a more complete sample? In other words, are there any other columns can help with this issue?

For example, it's hard to find level 3 for "J". 

Bill_of_Material_Totals2

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hk2018086 Regular Visitor
Regular Visitor

Re: Bill of Material Totals

Hi Dale

the parent-child relationship is defined by level column 1 is a parent of 2 and 2 is a parent of 3 and so on. Transforming this data is what I am finding it hard. After the right transformation, it is a simple product of child qty with every parent qty above it.

Super User
Super User

Re: Bill of Material Totals

Hi @hk2018086,

it looks as if you have trouble using my solution: https://www.thebiccountant.com/2017/05/08/dynamic-bill-of-material-bom-solution-in-excel-and-powerbi...

What exactly are you struggling with?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




hk2018086 Regular Visitor
Regular Visitor

Re: Bill of Material Totals

hi Imke

 

thank you for reviewing this. I am glad you saw this. your solution works when the source data is formatted correctly. I have quoted your blog below. The issue is that my input data is in the format that I showed in the question.

 

From your blog:

"The format of the input-data for this function needs to be like the example used from the Adventure Works 2008-database, where all products on the top of the hierarchy also have an entry in the child-column (the components), leaving the parent column blank:"

Super User
Super User

Re: Bill of Material Totals

You can create that structure if you append the top parents as childs (without parents) to your original table (assuming that's called "Source") like this:

 

Source & Table.FromColumns({List.Difference(Source[Parent], Source[Child])}, {"Child"})

You might have to replace the "Parent" & "Child"-column names by the column names of your table.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries