SUM OF PREVIOUS columns , SAME ROW, conditional formating

[razieh12]

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.

[lbendlin]

Similarly, in City 2, if 7+1+4 exceeds 12 for Station C,

It doesn't exceed 12.

[razieh12]

it is greater than or equal 12 

[lbendlin · Accepted solution]

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

[razieh12]

Thanks, it works