cancel
Showing results for
Did you mean:
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.

24 REPLIES 24
Highlighted
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.

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.
Regular Visitor

## Re: Bill of Material Totals

Just clarified the question, please check it now.

Community Support Team

## Re: Bill of Material Totals

Hi @hk2018086,

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.
Regular Visitor

## Re: Bill of Material Totals

hi Dale

updated the question to make it clear.

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".

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.
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

## 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?

Proud to be a Datanaut!

Imke Feldmann

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.

"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

## 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.