topic Re: Dynamic Running Total in Parent-Child Hierarchy in DAX Commands and Tips

Dynamic Running Total in Parent-Child Hierarchy

eugentov — Wed, 17 Aug 2022 07:28:38 GMT

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).

Re: Dynamic Running Total in Parent-Child Hierarchy

lbendlin — Sun, 21 Aug 2022 22:41:29 GMT

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

Re: Dynamic Running Total in Parent-Child Hierarchy

eugentov — Mon, 22 Aug 2022 06:40:42 GMT

correct

Re: Dynamic Running Total in Parent-Child Hierarchy

lbendlin — Mon, 22 Aug 2022 20:04:26 GMT

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))