Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
Solved! Go to 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))
Wait what, each hierarchy level has their own cost plus the cost of all their lower levels?!?
correct
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |