Bill of Material Totals

[hk2018086]

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.

[hk2018086]

I got this to work partially. it breaks if the depth of the total number of levels changes. I still need help with that step.

please see the attached file.

Multilevel totals

[v-jiascu-msft]

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

[hk2018086]

Just clarified the question, please check it now.

[v-jiascu-msft]

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

[hk2018086]

hi Dale

updated the question to make it clear.

[v-jiascu-msft]

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

[hk2018086]

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.

[ImkeF]

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?

[hk2018086]

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

[ImkeF]

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.

[hk2018086]

Hi Imke

My table has only these 3 columns. I did not understand how to convert this a parent child form. Can you please elaborate?

Level Part Qty

1A1

2B5

3C3

4D2

2E1

3F1

[ImkeF]

Sorry, I didn't read your thread throroughly enough. What would the result for E and F be?: Each 1?

So the logic would be to roll everything up towards 1 by multiplying it?

Are the Level/Part-combinations exclusive?

(This use case looks a bit different to the parent-child-hierarchy from my example).

[hk2018086]

yes E and F would be one. Level/part combinations are not exclusive. I have posted a partial solution. Need help with a step where multiple columns are multiplied and the number of columns varies due to changing levels. please review the excel file i shared as a reply to my question.. there is a link.

[ImkeF]

thanks, but your sample is not clear to me. How would the results be if the first row: Level:1 Part:A would be 10 instead of 1? Would this impact the result of the yellow figures?

[hk2018086]

Yes it would impact. If the parent level increases then all children get multiplied by it.

How can I deal with the multiplication of varying number of columns generated by pivoting the levels?

[ImkeF]

You select the fields that are lower thant the level of the current row like this:

 Record.SelectFields([PivotedFields], List.Select(Record.FieldNames([PivotedFields]), (x)=> Number.From(x)<=[#"Level - Copy"]))

Paste this code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkOlWJ1oJSMgywmITcE8YyDLGYiNwTwTIMsFiI3gKl3h+kAq3VB4LhBTYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Level = _t, Part = _t, qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Level", Int64.Type}, {"Part", type text}, {"qty", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Added Index1",JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Level"}, {"Previous.Level"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Group", each if [Previous.Level] >= [Level] then [Index] else null),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Group", 0}}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Replaced Errors",{{"Index", Order.Ascending}})),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Group"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index", "Index.1", "Previous.Level"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Level", "Level - Copy"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Duplicated Column", {{"Level", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Duplicated Column", {{"Level", type text}}, "en-GB")[Level]), "Level", "qty", List.Sum),
    #"Filled Down1" = Table.FillDown(#"Pivoted Column", Table.ColumnNames(#"Pivoted Column")),
    SelectAllPivotedFields = Table.AddColumn(#"Filled Down1", "PivotedFields", each Record.SelectFields(_, List.Difference(Record.FieldNames(_), {"Part", "Group", "Level - Copy"}))),
    SelectPivotFieldsWithLowerLevel = Table.AddColumn(SelectAllPivotedFields, "SelectedPivotFields", each Record.SelectFields([PivotedFields], List.Select(Record.FieldNames([PivotedFields]), (x)=> Number.From(x)<=[#"Level - Copy"]))),
    MultiplyAllElements = Table.AddColumn(SelectPivotFieldsWithLowerLevel, "RolledUpQty", each List.Product(Record.FieldValues([SelectedPivotFields]))),
    #"Grouped Rows" = Table.Group(MultiplyAllElements, {"Part"}, {{"TotalQty", each List.Sum([RolledUpQty]), type number}})
in
    #"Grouped Rows"

[hk2018086]

Hi Imke

Part D is not totaling correctly with the code that you supplied. Can you please recheck it?

LevelPartqty

1	A	1
2	B	5
3	C	3
4	D	10
2	E	2
3	F	1
3	D	5

[ImkeF]

Is this your desired result? 

Unfortunately I don't understand the desired aggregation logic, so please give some samples of the desired mathematical operations behind it.

[hk2018086]

hi imke,

D come at two places in my last reply. onetime as in the parent chain of A and another time in the parent chain of E.

Under the chain of A the total for D is 1x5x3x10=150. Under the chian  of E the total for D is 2x1x5=10. so total for D is 150+10=160

[ImkeF]

Thanks, that's a step further, but I have the impression, that I still don't see the full picture.

So please give a representative example of your source data and the full desired result. 

Please also explain what role exactly the levels play and include data in your sample that show their role.