Tree Calculation/Aggregated Values Matrix

[ivandgreat]

im struggling to create a table parent and child tree relationship, i have a parent and child table, i wanted to calculate the values with respect to their root parents like a family tree

Child	Parent	Child Value
Site1	Site2	10
Site2	Site3	20
Site3	Site4	15
Site5	Site4	5
Site4	Site6	20
Site6		(No Parent or Parent column is null value)

The output should be coming from the Parent Column for column Child item

Child	Parent	Child Value	Tree Relation	Calculated Values
Site1	Site2	10	Site1,Site2,Site3,Site4,Site6	10+20+15+20+0(null)=65
Site2	Site3	20	Site2,Site3,Site4,Site6	20+15+20+0(null)=55
Site3	Site4	15	Site3,Site4,Site6	15+20+0(null)=35
Site5	Site4	5	Site5,Site4,Site6	5+20+0(null) =25
Site4	Site6	20	Site4,Site6	20+0(null) =20
Site6			Site6	0(null)=0

I don't know if possible to create this using DAX or m function . Thanks in advance

[dufoq3]

Hi @ivandgreat, I finaly did it in Power Query, but it took me more than an hour... (I'm glad for this experience).

Both versions will work only if there are no duplicates in [Child] column.

Result:

v1 (slower)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSTVU0gHTRkDa0EApVicazgXRxkDaCCFsDBU2Aak2hQubIgkjRE2gomaoZoC4IBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Child = _t, Parent = _t, #"Child Value" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Child Value", type number}}),
    // This step is also mandantory!
    RenamedColumns1 = Table.RenameColumns(ChangedType,{{"Parent", "Parent1"}}),
    Buffer = Table.Buffer(Table.SelectColumns(RenamedColumns1, {"Child", "Parent1", "Child Value"})),
    ParentsList = List.Buffer(List.Distinct(List.Select(Buffer[Parent1], (x)=> x <> null and Text.Trim(x) <> ""))),
    ParentsHierarchy = Table.AddColumn(Table.FromList(ParentsList, Splitter.SplitByNothing(), {"Parent1"}), "Parents", each List.Accumulate( 
    { 0..List.Count(ParentsList) -2 },
    [ Parent1 = [Parent1], Parent1 Value = try Buffer{[Child = Parent1]}[Child Value] otherwise null, Parent1 Parent = try Buffer{[Child = Parent1]}[Parent1] otherwise null ],
    (s,c)=> s & (if (try Buffer{[Child = Record.Field(s, "Parent" & Text.From(c+1) & " Parent")]}[Parent1] otherwise null) = null
                then [] else
                Record.FromList( {  try Buffer{[Child = Record.Field(s, "Parent" & Text.From(c+1))]}[Parent1] otherwise null,                 //Parent
                                    try Buffer{[Child = Record.Field(s, "Parent" & Text.From(c+1) & " Parent")]}[Child Value] otherwise null, //Parent Child Value
                                    try Buffer{[Child = Record.Field(s, "Parent" & Text.From(c+1) & " Parent")]}[Parent1] otherwise null      //Parent's Parent
                                 }, { "Parent" & Text.From(c+2), "Parent" & Text.From(c+2) & " Value", "Parent" & Text.From(c+2) & " Parent" } ) ) )    //Headers
  , type list)
,
    StepBack = RenamedColumns1,
    MergedQueryItself = Table.NestedJoin(StepBack, {"Parent1"}, ParentsHierarchy, {"Parent1"}, "ParentsHierarchy", JoinKind.LeftOuter),
    ExpandedParentsHierarchy = Table.ExpandTableColumn(MergedQueryItself, "ParentsHierarchy", {"Parents"}, {"Parents"}),
    Ad_TreeRelation = Table.AddColumn(ExpandedParentsHierarchy, "Tree Relation", each
     try Text.Combine(List.Select(Record.ToList(Record.SelectFields([Parents], List.Select(Record.FieldNames([Parents]), (x)=> not Text.Contains(x, " ")))), (y)=> y <> null and Text.Trim(y) <> ""), ", ") otherwise null,
  type text),
    Ad_CalculatedValues = Table.AddColumn(Ad_TreeRelation, "Calculated Values", each
     try List.Sum({[Child Value]} & { List.Sum(Record.ToList(Record.SelectFields([Parents], List.Select(Record.FieldNames([Parents]), (x)=> Text.Contains(x, "Value"))))) }) otherwise null,
  type number),
    RemovedColumns = Table.RemoveColumns(Ad_CalculatedValues,{"Parents"})
in
    RemovedColumns

v2 (faster)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSTVU0gHTRkDa0EApVicazgXRxkDaCCFsDBU2Aak2hQubIgkjRE2gomaoZoC4IBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Child = _t, Parent = _t, #"Child Value" = _t]),
    ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Child", "Parent"}),
    ChangedType = Table.TransformColumnTypes(ReplacedValue,{{"Child Value", type number}}),
    ParentsList = List.Buffer(List.Distinct(List.Select(ChangedType[Parent], (x)=> x <> null and Text.Trim(x) <> ""))),
    ParentsHierarchy = Table.AddColumn(Table.FromList(ParentsList, Splitter.SplitByNothing(), {"Parent"}), "Parents", each List.RemoveNulls(List.Accumulate(
    { 0..List.Count(ParentsList) -1 },
    {[Parent]},
    (s,c)=> s & { if s{c} = null then null else try ReplacedValue{[Child = s{c}]}[Parent] otherwise null } 
  )), type list),
    StepBack = ChangedType,
    MergedQueryParrentsHierarchy = Table.NestedJoin(StepBack, {"Parent"}, ParentsHierarchy, {"Parent"}, "ParentsHierarchy", JoinKind.LeftOuter),
    ExpandedParentsHierarchy = Table.ExpandTableColumn(MergedQueryParrentsHierarchy, "ParentsHierarchy", {"Parents"}, {"Parents"}),
    Ad_TreeRelation = Table.AddColumn(ExpandedParentsHierarchy, "Tree Relation", each try Text.Combine({[Child]} & [Parents], ", ") otherwise null, type text),
    ExpandedParents = Table.ExpandListColumn(Ad_TreeRelation, "Parents"),
    MergedQueryStepBack = Table.NestedJoin(ExpandedParents, {"Parents"}, StepBack, {"Child"}, "StepBack", JoinKind.LeftOuter),
    #"Expanded StepBack" = Table.ExpandTableColumn(MergedQueryStepBack, "StepBack", {"Child Value"}, {"Parrent Value"}),
    GroupedRows = Table.Group(#"Expanded StepBack", {"Child", "Parent", "Child Value", "Tree Relation"}, {{"Parent Calculated Values", each List.Sum([Parrent Value]), type nullable number}}),
    Ad_CalculatedValues = Table.AddColumn(GroupedRows, "Calculated Values", each [Parent Calculated Values] + [Child Value], type number),
    RemovedColumns = Table.RemoveColumns(Ad_CalculatedValues,{"Parent Calculated Values"})
in
    RemovedColumns

[wdx223_Daniel]

if Child Code is unique, Path Function can resolve it easily. 

with duplicated Child, DAX can not deal with this kind of recurrsion problem.

[ivandgreat]

i modify my table to be able to have a unique child. can you help me to have a solution its either on DAX or power query. Thanks in advance

[wdx223_Daniel]

[ivandgreat]

@wdx223_Daniel , Great, this works fine. May i know if its possible as well to get all the total for all grand child/children

Child	Parent	Value	Tree Relation	Calculate Grand Child
Site1	Site2	10	Site1	10
Site2	Site3	20	Site2,Site1	20+10=30
Site3	Site4	15	Site3,Site2,Site1	15+20+10=45
Site5	Site4	5	Site5	5
Site4	Site6	20	Site4,Site5,Site3,Site2,Site1	20+5+15+20+10=70
Site6		100	Site6,Site5,Site4,Site4,Site2,Site1	100+20+5+15+20+10=170

Thanks again.

[wdx223_Daniel · Accepted solution]

Tree Relation=CONCATENATEX(FILTER(Data,PATHCONTAINS(PATH(Data[Child],Data[Parent]),EARLIER(Data[Child]))),Data[Child],"|")

Calculated Values formula keeps no change.

[ivandgreat]

the relation is right, but the calculated value is wrong.

the formula i have is below but shows wrong ouptut.

CalValue = SUMX(FILTER('ParentChild',PATHCONTAINS(EARLIER('ParentChild'[Tree]),'ParentChild'[Child])),'ParentChild'[Value])

[ivandgreat]

Thanks . but i got a wrong computation on the calculated values..