Cumulative sumproduct in relational data table

[tonie_tollig]

Hi all,

I have 2 tables. The one table presents the sumproduct relation between different components.

Component ID	Component ID 2	Factor
NC 3	NC 1	14
NC 3	NC 2	19
NC 4	NC 2	15
NC 4	NC 3	16
NC 4	NC 1	9
NC 5	NC 2	17
NC 5	NC 3	9
NC 5	NC 4	14

For example NC3 = 14*NC1 + 19NC2. The table runs from the top down i.e. NC1 will not have a dependancy relationship to NC(x+1).

In addition I have an input table with multiple sets of input values for NC1 & NC.

Component ID	Input value 1	Input Value 2
NC 1	200000	20
NC 2	100000	50

The requirement is to build a combined factor for each component:

For example:

NC4 = 15*NC2 + 16*NC3+ 9*NC1 = 15* NC2 + 16*(14*NC2 + 19*NC1) + 9 *NC1

The combined factor will then be multipled by the different input values in the input table.

Many thanks, T

[Zubair_Muhammad · Accepted solution]

@tonie_tollig @Stachu

I believe there should be a more elegant solution

but this might work as well

The idea is to mege Child ID with Parent ID to get Grand Children and repeat this merge until there are no children other than "NC 1" and NC 2"

Please see the attached file as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNWMFbSAVGGQMrQRClWB1nQCCRoCRM0QRI0RRMEaTA0QxMEmQnXbYqk2xxN0BiLQhOog2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Component ID" = _t, #"Component ID 2" = _t, Factor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Component ID", type text}, {"Component ID 2", type text}, {"Factor", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Component ID 2"},#"Changed Type",{"Component ID"},"Changed Type",JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Component ID 2", "Factor"}, {"Component ID 3", "Factor3"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Changed Type",{{"Component ID", Order.Ascending}}),
    #"Merged Queries1" = Table.NestedJoin(#"Sorted Rows",{"Component ID 3"},#"Sorted Rows",{"Component ID"},"Sorted Rows",JoinKind.LeftOuter),
    #"Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Sorted Rows", {"Component ID 2", "Factor"}, {"Component ID 4", "Factor4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Sorted Rows",{{"Factor", "Factor2"}}),
    #"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"Component ID 2", "Component ID 3", "Component ID 4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Factor2", type text}, {"Factor3", type text}, {"Factor4", type text}}, "en-US"),{"Factor2", "Factor3", "Factor4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"),
    #"Sorted Rows1" = Table.Sort(#"Merged Columns1",{{"Merged", Order.Ascending}, {"Merged.1", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Custom", each List.RemoveItems(Text.Split([Merged],","),{" ",""})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Value.FromText(List.RemoveItems(Text.Split([Merged.1],","),{" ",""}))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Accumulate([Custom.1],1,(state, current) => Number.From(state) * Number.From(current))),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom2",{{"Custom.2", "Factor product"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns1", "Custom.2", each List.Intersect({[Custom],{"NC 1","NC 2"}})),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom3", {"Custom.2", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Merged", "Merged.1", "Custom", "Custom.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Component ID", "Custom.2", "Factor product"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"Custom.2", "Grand Children"}})
in
    #"Renamed Columns2"

[Zubair_Muhammad · Accepted solution]

Hello @tonie_tollig

You can use this M solution which uses a recursive function...Hopefully it will work with your entire list.

Please see attached file as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNWMFbSAVGGQMrQRClWB1nQCCRoCRM0QRI0RRMEaTA0QxMEmQnXbYqk2xxN0BiLQhOog2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Component ID" = _t, #"Component ID 2" = _t, Factor = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Component ID", type text}, {"Component ID 2", type text}, {"Factor", Int64.Type}}),
    mytable=Table.Buffer(ChangedType),
    mygrandchildren={"NC 1","NC 2"},
    otherchildren=List.Distinct(List.RemoveItems(mytable[Component ID 2],mygrandchildren)),
    myfunction=(SourceTable)=>
    let
    Mvar= each  + 1,
    myoutput=Table.NestedJoin(SourceTable,{"Component ID 2"},SourceTable,{"Component ID"},"Step",JoinKind.LeftOuter),
    myexpand=Table.ExpandTableColumn(myoutput, "Step", {"Component ID 2", "Factor"}, {"Grand Children", "Factor2"}),
    myexpand1=Table.AddColumn(myexpand, "Factor3", each [Factor] * (if [Factor2]=null then 1 else [Factor2]), type number),
    myexpand2=Table.AddColumn(Table.FromColumns({myexpand1[Component ID],myexpand1[Grand Children],myexpand1[Factor3],myexpand1[Component ID 2]},{"Component ID","Component ID 2","Factor","Component ID s"}),"GC 1",each if [Component ID 2]=null then [Component ID s] else [Component ID 2]),
    myexpand3=Table.FromColumns({myexpand2[Component ID],myexpand2[GC 1],myexpand2[Factor]},{"Component ID","Component ID 2","Factor"})
    in
if List.ContainsAny(myexpand3[Component ID 2],otherchildren) then @ myfunction(myexpand3) else myexpand3,
    FinalTable=myfunction(mytable),
    #"Sorted Rows" = Table.Sort(FinalTable,{{"Component ID", Order.Ascending}, {"Factor", Order.Ascending}})
in
    #"Sorted Rows"

[Stachu]

PowerBI doesn't really respect the order of the data, so you will have to work around to mimic the top-down logic of your data

the problem would be trivial if you could transform Table1 to only input measures, i.e.

Component ID	Component ID 2	Input	Factor
NC 3	NC 1	NC 1	14
NC 3	NC 2	NC 2	19
NC 4	NC 2	NC 2	15
NC 4	NC 3	NC 1	16
NC 4	NC 3	NC 2	16
NC 4	NC 1	NC 1	9
NC 5	NC 2	NC 2	17
NC 5	NC 3	NC 1	9
NC 5	NC 3	NC 2	9
NC 5	NC 4	NC 2	14
NC 5	NC 4	NC 1	14
NC 5	NC 4	NC 2	14
NC 5	NC 4	NC 1	14

in order to do that you will need recursion which is available in M. Is that a viable option for you?
I'm not sure you could achieve what you need just with DAX

[tonie_tollig]

I would prefer to do it in DAX because its more transparent as part of a bigger modelling project. If not possible in DAX, I have the option to do it in M.

I am still relatively new to DAX/M. Will it be possible to give me some pointers?

Tks

T

[Stachu]

hmm, I think I misunderstood the problem, basically I thought that

NC3 = NC2+NC1 

rather than

NC3= 14*NC1+19*NC2

then it was just a simple SUMX with RELATED in DAX

you could expand the list the way I showed with this function

(lst as list) =>
[ 
fnGetComponentsList = (lst as list)=>
let
    basic = {"NC 1", "NC 2"},
    tbl = Table1,
    col1 = "Component ID",
    col2 = "Component ID 2",
    Zip = List.Zip({Table.Column(tbl,col1), Table.Column(tbl,col2)}),
    SelectRelevant = List.Select(Zip, each List.Contains(lst, _{0}) or List.Contains(basic, _{0})),
    GetComp = List.Transform(SelectRelevant, each _{1}),
    Output = List.Combine({GetComp, List.Intersect({lst, basic})})
in
    Output,        
fnGetInputIDs = (y as list)=>
    if fnGetComponentsList(y) = y
    then y 
    else @fnGetComponentsList(fnGetComponentsList(y)),
Output = @fnGetInputIDs(lst) 
][Output]

but it's missing the factor multiplication
you could add Factor there, but it's becoming more complex that way and I don't think that's a good solution

maybe @Greg_Deckler, @Zubair_Muhammad or @ImkeF can help you, I'm at a loss here

[Zubair_Muhammad · Accepted solution]

@tonie_tollig @Stachu

I believe there should be a more elegant solution

but this might work as well

The idea is to mege Child ID with Parent ID to get Grand Children and repeat this merge until there are no children other than "NC 1" and NC 2"

Please see the attached file as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNWMFbSAVGGQMrQRClWB1nQCCRoCRM0QRI0RRMEaTA0QxMEmQnXbYqk2xxN0BiLQhOog2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Component ID" = _t, #"Component ID 2" = _t, Factor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Component ID", type text}, {"Component ID 2", type text}, {"Factor", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Component ID 2"},#"Changed Type",{"Component ID"},"Changed Type",JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Component ID 2", "Factor"}, {"Component ID 3", "Factor3"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Changed Type",{{"Component ID", Order.Ascending}}),
    #"Merged Queries1" = Table.NestedJoin(#"Sorted Rows",{"Component ID 3"},#"Sorted Rows",{"Component ID"},"Sorted Rows",JoinKind.LeftOuter),
    #"Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Sorted Rows", {"Component ID 2", "Factor"}, {"Component ID 4", "Factor4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Sorted Rows",{{"Factor", "Factor2"}}),
    #"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"Component ID 2", "Component ID 3", "Component ID 4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Factor2", type text}, {"Factor3", type text}, {"Factor4", type text}}, "en-US"),{"Factor2", "Factor3", "Factor4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"),
    #"Sorted Rows1" = Table.Sort(#"Merged Columns1",{{"Merged", Order.Ascending}, {"Merged.1", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Custom", each List.RemoveItems(Text.Split([Merged],","),{" ",""})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Value.FromText(List.RemoveItems(Text.Split([Merged.1],","),{" ",""}))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Accumulate([Custom.1],1,(state, current) => Number.From(state) * Number.From(current))),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom2",{{"Custom.2", "Factor product"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns1", "Custom.2", each List.Intersect({[Custom],{"NC 1","NC 2"}})),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom3", {"Custom.2", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Merged", "Merged.1", "Custom", "Custom.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Component ID", "Custom.2", "Factor product"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"Custom.2", "Grand Children"}})
in
    #"Renamed Columns2"

[tonie_tollig]

Thank you for the effort.

It is unfortunately a very customised solution. I will eventually have an entire list of NCs with different recursive relationships which will dynamically change.

[Zubair_Muhammad · Accepted solution]

Hello @tonie_tollig

You can use this M solution which uses a recursive function...Hopefully it will work with your entire list.

Please see attached file as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNWMFbSAVGGQMrQRClWB1nQCCRoCRM0QRI0RRMEaTA0QxMEmQnXbYqk2xxN0BiLQhOog2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Component ID" = _t, #"Component ID 2" = _t, Factor = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Component ID", type text}, {"Component ID 2", type text}, {"Factor", Int64.Type}}),
    mytable=Table.Buffer(ChangedType),
    mygrandchildren={"NC 1","NC 2"},
    otherchildren=List.Distinct(List.RemoveItems(mytable[Component ID 2],mygrandchildren)),
    myfunction=(SourceTable)=>
    let
    Mvar= each  + 1,
    myoutput=Table.NestedJoin(SourceTable,{"Component ID 2"},SourceTable,{"Component ID"},"Step",JoinKind.LeftOuter),
    myexpand=Table.ExpandTableColumn(myoutput, "Step", {"Component ID 2", "Factor"}, {"Grand Children", "Factor2"}),
    myexpand1=Table.AddColumn(myexpand, "Factor3", each [Factor] * (if [Factor2]=null then 1 else [Factor2]), type number),
    myexpand2=Table.AddColumn(Table.FromColumns({myexpand1[Component ID],myexpand1[Grand Children],myexpand1[Factor3],myexpand1[Component ID 2]},{"Component ID","Component ID 2","Factor","Component ID s"}),"GC 1",each if [Component ID 2]=null then [Component ID s] else [Component ID 2]),
    myexpand3=Table.FromColumns({myexpand2[Component ID],myexpand2[GC 1],myexpand2[Factor]},{"Component ID","Component ID 2","Factor"})
    in
if List.ContainsAny(myexpand3[Component ID 2],otherchildren) then @ myfunction(myexpand3) else myexpand3,
    FinalTable=myfunction(mytable),
    #"Sorted Rows" = Table.Sort(FinalTable,{{"Component ID", Order.Ascending}, {"Factor", Order.Ascending}})
in
    #"Sorted Rows"

[tonie_tollig · Accepted solution]

Thank you very much. I will test this!