Forum Discussion

KL718's avatar
KL718
Frequent Visitor
7 years ago
Solved

Cumulative Total

I want to calculate cumulative total. I have gone through other post but my dataset do not have Dates column.  
Need Help
Show and Tell
hnguy71's avatar
hnguy71
Super User
7 years ago

You can achieve this via Power Query:

 

The meat of the code works starting from GroupMe and all the way down:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyNAASSrE6QK4RkGWE4BoDWcYQbiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Part = _t, Sales = _t, dd = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", type text}, {"Sales", Int64.Type}}),
    GroupMe = Table.AddColumn(#"Changed Type", "GroupMe", each 1),
    #"Added Index" = Table.AddIndexColumn(GroupMe, "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"GroupMe"}, {{"All_Rows", each _, type table}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "All_Rows", "All_Rows - Copy"),
    #"Expanded All_Rows" = Table.ExpandTableColumn(#"Duplicated Column", "All_Rows", {"Part", "Sales", "Index"}, {"Part", "Sales", "Index"}),
    #"Expanded All_Rows - Copy" = Table.ExpandTableColumn(#"Expanded All_Rows", "All_Rows - Copy", {"Part", "Sales", "Index"}, {"Part.1", "Sales.1", "Index.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All_Rows - Copy", "Find_Cumulative", each [Index] >= [Index.1]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Find_Cumulative] = true)),
    #"Regroup for Cumulative" = Table.Group(#"Filtered Rows", {"GroupMe", "Sales", "Index", "Part"}, {{"Cumulative Total", each List.Sum([Sales.1]), type number}})
in
    #"Regroup for Cumulative"