Forum Discussion
Cumulative sum Column
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
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])))
Hello everyone sorry for the hassle I need help with a column accumulated in power query for a dashboard in power bi where I need a column (Runnig t) adding the values (ve) depending on the variables (Est) and (CICL) as I show in the example. for your help I am grateful.
Recueden I need the code in M language
Hi,
Share data in a format that can be pasted in an MS Excel file.
Pond Cycle Raleo LEB5 C2-20 2132 LEB5 C2-20 3554 LEB5 C2-20 4549 LEB12 C2-20 1653 LEB12 C2-20 3203 LEB12 C2-20 4339 LEB5 C1-21 3152 LEB5 C1-21 4063 LEB5 C1-21 5618 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
AddedRunningSumI don´t Know what is wrong.
Help me please
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.
Thanks for all