Forum Discussion
Daily Operation Job Count Plan
Hi I am new to Power BI and this group, so apologies if this is a daft question but help would be appreciated, thank you. I have a set of data that shows daily orders booked in by operation. I also...
In the diagram that i showed I displayed the 'daily number' and the 'brought forward number' together to maximise capacity. These numbers were visible rather than a total. Can I as, will this solution still show the breakdown? Thank you in advance
The following code should show the breakdown...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyNAASJkBsDsSWQGwMxGZgsVgdoBojINMUiI2gUsZQtglYPDYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Operation Number" = _t, Capacity = _t, #"08/18/2023" = _t, #"08/19/2023" = _t, #"08/20/2023" = _t, #"08/21/2023" = _t, #"08/22/2023" = _t, #"08/23/2023" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Operation Number", "Capacity"}, "Date", "Order Count"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Operation Number"}, {{"Count", each _, type table [Operation Number=nullable text, Capacity=nullable number, Date=text, Order Count=number]}}),
Custom1 = Table.AddColumn(#"Grouped Rows", "IndexAdded", each Table.AddIndexColumn([Count],"Index",0, 1)),
#"Removed Columns" = Table.RemoveColumns(Custom1,{"Count"}),
#"Expanded Index" = Table.ExpandTableColumn(#"Removed Columns", "IndexAdded", {"Capacity", "Date", "Order Count", "Index"}, {"Capacity", "Date", "Order Count", "Index"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Index",{{"Operation Number", type text}, {"Capacity", Int64.Type}, {"Date", type date}, {"Order Count", Int64.Type}, {"Index", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "runningTotal", each List.Sum(Table.SelectRows(#"Changed Type", (y)=> [Operation Number] = y[Operation Number] and [Date] >= y[Date])[Order Count]), type number),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "newDaily", each if [runningTotal]-([Index] * [Capacity]) < 0 then 0 else [runningTotal]-([Index] * [Capacity]), type number),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Operation Number Order Sum", each List.Sum(Table.SelectRows(#"Added Custom1", (x)=> [Operation Number] = x[Operation Number])[Order Count]), type number),
Custom2 = Table.AddColumn(#"Added Custom2", "maxIndex", each List.Max(Table.SelectRows(#"Added Custom2", each Number.Mod([Operation Number Order Sum], [Capacity] * ([Index]+1)) <> [Operation Number Order Sum])[Index]), type number),
Custom3 = Table.AddColumn(Custom2, "pulledAheadCount", each (if [Index] <= [maxIndex] then [Capacity] else if [Index] = [maxIndex] + 1 then [Operation Number Order Sum] - (([maxIndex] + 1) * [Capacity]) else 0) - [newDaily], type number),
#"Added Custom3" = Table.AddColumn(Custom3, "Maximized Daily Count", each Number.ToText([newDaily]) & "/" & Number.ToText([pulledAheadCount]), type text),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"Order Count", "Index", "runningTotal", "newDaily", "Operation Number Order Sum", "maxIndex", "pulledAheadCount"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Date", type text}}, "en-US")[Date]), "Date", "Maximized Daily Count")
in
#"Pivoted Column"Thank you very much
