cancel
Showing results for
Did you mean:
Regular Visitor

## Re: Bill of Material Totals

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

Super User

## Re: Bill of Material Totals

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

Proud to be a Datanaut!

Imke Feldmann

Regular Visitor

## Re: Bill of Material Totals

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.

Multilevel totals

Regular Visitor

## Re: Bill of Material Totals

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.

Super User

## Re: Bill of Material Totals

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?

Proud to be a Datanaut!

Imke Feldmann

Regular Visitor

## Re: Bill of Material Totals

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?

Super User

## Re: Bill of Material Totals

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"]))`

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

Proud to be a Datanaut!

Imke Feldmann

Regular Visitor

## Re: Bill of Material Totals

Totally awesome and much more elegant. thank you

Regular Visitor

## Re: Bill of Material Totals

Hi Imke

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

LevelPartqty

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

## Re: Bill of Material Totals

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

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 378 members 4,366 guests
Recent signins: