Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cumulative sum Column

Hello Everyone!   I have a table like this, with the sales each week, i want to calculate a cumulative sum for each month.  The table has sales for each month: I have the table like this:    ...
Need Help
Show and Tell
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    you can create a column

    Column = CALCULATE(sum(Sheet6[ Sales Week]),FILTER(Sheet6,Sheet6[Period]=EARLIER(Sheet6[Period])&&Sheet6[Week]<=EARLIER(Sheet6[Week])))

    please see the attachment below

  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    please try this

    Column = CALCULATE(sum(Sheet6[ Sales Week]),FILTER(Sheet6,Sheet6[Period]=EARLIER(Sheet6[Period])&&Sheet6[Week]<=EARLIER(Sheet6[Week])&&'Sheet6'[Product]=EARLIER(Sheet6[Product])))
Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

PondCycleRaleo
LEB5C2-202132
LEB5C2-203554
LEB5C2-204549
LEB12C2-201653
LEB12C2-203203
LEB12C2-204339
LEB5C1-213152
LEB5C1-214063
LEB5C1-215618

Mcode

let
Origen = Excel.Workbook(File.Contents("C:\Users\J024919\Downloads\Prub.xlsx"), null, true),
Est_Table = Origen{[Item="Est",Kind="Table"]}[Data],
#"Tipo cambiado" = Table.TransformColumnTypes(Est_Table,{{"Estanque", type text}, {"Ciclo", type text}, {"Raleo", type number}}),
TableType = Value.Type(Table.AddColumn(#"Tipo cambiado", "Running Sum", each null, type number)),
#"Grouped Rows" = Table.Group(Est_Table, {"Estanque", "Ciclo"}, {{"AllData", each fnAddRunningSum (_, "Raleo"), TableType}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Raleo", "Running Sum"}, {"Raleo", "Running Sum"})
in #"Expanded AllData"

Code function =

(MyTable as table, value as text) as table =>
let
Source = Table.Buffer(Est),
value = "Ciclo",
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Table.Column(Source, value),{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + Number.From(cost)})),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
AddedRunningSum

I don´t Know what is wrong.

Help me please

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pond", type text}, {"Cycle", type text}, {"Raleo", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Merged Columns" = Table.CombineColumns(#"Added Index",{"Pond", "Cycle"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    BufferedTable = Table.Buffer(#"Merged Columns"),
    Custom1 = Table.AddColumn(
    BufferedTable, 
    "Running Total", 
    (OutTable) => 
        List.Sum(
            Table.SelectRows(
                BufferedTable, 
                (InTable) => InTable[Index] <= OutTable[Index]
                and 
                InTable[Merged] = OutTable[Merged])[Raleo]
        )
    ),
    #"Split Column by Delimiter" = Table.SplitColumn(Custom1, "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged.1", "Pond"}, {"Merged.2", "Cycle"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Running Total", Int64.Type}})
in
    #"Changed Type1"

Hope this helps.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    Thanks for all