Tree Calculation/Aggregated Values Matrix

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

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

Hi Anonymous, 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