Forum Discussion

tonie_tollig's avatar
tonie_tollig
Frequent Visitor
7 years ago
Solved

Cumulative sumproduct in relational data table

Hi all,

 

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

 

Component IDComponent ID 2Factor
NC 3NC 114
NC 3NC 219
NC 4NC 215
NC 4NC 316
NC 4NC 19
NC 5NC 217
NC 5NC 39
NC 5NC 414

 

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 IDInput value 1Input Value 2
NC 120000020
NC 210000050

 

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

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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's avatar
    Zubair_Muhammad
    7 years ago

    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"

     

7 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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 IDComponent ID 2InputFactor
    NC 3NC 1NC 114
    NC 3NC 2NC 219
    NC 4NC 2NC 215
    NC 4NC 3NC 116
    NC 4NC 3NC 216
    NC 4NC 1NC 19
    NC 5NC 2NC 217
    NC 5NC 3NC 19
    NC 5NC 3NC 29
    NC 5NC 4NC 214
    NC 5NC 4NC 114
    NC 5NC 4NC 214
    NC 5NC 4NC 114

    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's avatar
      tonie_tollig
      Frequent Visitor
      7 years ago

      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's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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