Dynamic Running Total in Parent-Child Hierarchy

[eugentov]

Hello,

I have a list of elements [1 through 7] which are hierarchly ordered as shown in the screenshot below (see diagram).

Each element has its own cost (column F). Through a Path Function I was able to trace the Parent-Child Relationship using Power Pivot / Power BI. I would like to know how cna I calculate column G (Dynamic Running Total Costs) in Power DAX, and if there is a way to replicate this in Excel. 

You may see on column I shows how column G was calculated.

Additionally, it is desired that if data is filtered out (i.e., 5 is taken out) it will reflect on the remaining entries (running cost would decrease).

 

[lbendlin]

Wait what, each hierarchy level has their own cost plus the cost of all their lower levels?!?

[eugentov]

correct

[lbendlin · Accepted solution]

You can use PATHCONTAINS() as a filter for this.

Source data Table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYvBDQAgCAN36ZuHAuowhP3XEBAfJHctNcME1S0ZcDLwd30uwZlp99o++fnKLr37HZybnp/AXe9wvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sort Number" = _t, Parent = _t, #"Total Cost" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Total Cost", Currency.Type}})
in
    #"Changed Type"

Calculated columns:

Path = PATH('Table'[Sort Number],'Table'[Parent])

Cumulative = 
var s= [Sort Number]
return CALCULATE(sum('Table'[Total Cost]),ALL('Table'),PATHCONTAINS('Table'[Path],s))