Forum Discussion
SUM OF PREVIOUS columns , SAME ROW, conditional formating
Hello
I'm currently working on a task and would appreciate your assistance. I'm managing three cities, each with a fixed bicycle capacity. Additionally, there are eight bicycle stations, and the capacity of each station changes based on demand. The stations need to be filled in a sequential order within each city. If the total number of bicycles needed to fill the stations exceeds the capacity, I would like the respective station to be highlighted. When this occurs, the city's capacity will be filled to its maximum.
for example:
I want to create this matrix in the Powerbi matrix . Rows are City name and Capacity.
And column is Stations. And value is “number of requests”.
Given the sequential nature of station filling, for instance, if 12+13 exceeds 20 for Station B in City 1, I wish to have Station B highlighted. Additionally, when we fill Station B, the city's capacity is replenished back to 20. Consequently, in Station E, we might encounter a shortage again, as 10+7+15 would be greater than or equal to 20.
Similarly, in City 2, if 7+1+4 exceeds 12 for Station C, I desire to highlight Station C. Likewise, Station G should be highlighted if 3+2+3+5 surpasses 12.
There is an issue with City3 - the mark should be on station G, not H.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4sqTRU0lEyMgAShkYgwhhEgLjmIIYpkLAAMcACsToQLUYw1eZQKRMgBmk0gtKmYHGYcmOoISZQFSZQXUZIuoCqYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Capacity = _t, A = _t, B = _t, C = _t, D = _t, E = _t, F = _t, G = _t, H = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"City", "Capacity"}, "Station", "Reported"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Capacity", Int64.Type}, {"Reported", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"City"}, {{"Rows", each _, type table [City=nullable text, Capacity=nullable number, Station=text, Reported=nullable number]}}), Compute = (tbl)=> let #"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Full", each List.Accumulate({0..[Index]},[cumul=0],(state,current)=>[ cumul = if #"Added Index"[Capacity]{current}<=state[cumul] + #"Added Index"[Reported]{current} then 0 else state[cumul] + #"Added Index"[Reported]{current}])) in #"Added Custom", AddedCompute = Table.AddColumn(#"Grouped Rows","comp",each Compute([Rows])), #"Expanded comp" = Table.ExpandTableColumn(AddedCompute, "comp", {"Capacity", "Station", "Reported", "Full"}, {"Capacity", "Station", "Reported", "Full"}), #"Expanded Full" = Table.ExpandRecordColumn(#"Expanded comp", "Full", {"cumul"}, {"cumul"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Full",{"City", "Capacity", "Station", "Reported", "cumul"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Capacity", Int64.Type}, {"Station", type text}, {"Reported", Int64.Type}, {"cumul", Int64.Type}}) in #"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
The yellow mark is equivalent to cumul=0
4 Replies
Similarly, in City 2, if 7+1+4 exceeds 12 for Station C,It doesn't exceed 12.
it is greater than or equal 12
There is an issue with City3 - the mark should be on station G, not H.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4sqTRU0lEyMgAShkYgwhhEgLjmIIYpkLAAMcACsToQLUYw1eZQKRMgBmk0gtKmYHGYcmOoISZQFSZQXUZIuoCqYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Capacity = _t, A = _t, B = _t, C = _t, D = _t, E = _t, F = _t, G = _t, H = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"City", "Capacity"}, "Station", "Reported"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Capacity", Int64.Type}, {"Reported", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"City"}, {{"Rows", each _, type table [City=nullable text, Capacity=nullable number, Station=text, Reported=nullable number]}}), Compute = (tbl)=> let #"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Full", each List.Accumulate({0..[Index]},[cumul=0],(state,current)=>[ cumul = if #"Added Index"[Capacity]{current}<=state[cumul] + #"Added Index"[Reported]{current} then 0 else state[cumul] + #"Added Index"[Reported]{current}])) in #"Added Custom", AddedCompute = Table.AddColumn(#"Grouped Rows","comp",each Compute([Rows])), #"Expanded comp" = Table.ExpandTableColumn(AddedCompute, "comp", {"Capacity", "Station", "Reported", "Full"}, {"Capacity", "Station", "Reported", "Full"}), #"Expanded Full" = Table.ExpandRecordColumn(#"Expanded comp", "Full", {"cumul"}, {"cumul"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Full",{"City", "Capacity", "Station", "Reported", "cumul"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Capacity", Int64.Type}, {"Station", type text}, {"Reported", Int64.Type}, {"cumul", Int64.Type}}) in #"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
The yellow mark is equivalent to cumul=0
