Cumulative Sum if previous row in another column is the same

user_13377037 0
Viewed 635

I'm trying to do something that is simple in excel but I need it to be added in my query. I am trying to create a cumulative sum in a new column of time values in one column if a name column equals a previous row. Example of before and after below

Data Input Table

ID Time
A 2
B 3
C 1
D 0.5
E 1
E 3
E 5
F 2
G 3
G 4
H 1

Table After Query

ID Time BeforeStart
A 2 0
B 3 0
C 1 0
D 0.5 0
E 1 0
E 3 1
E 5 4
F 2 0
G 3 0
G 4 3
H 1 0

Basically if column ID equals the row above itself then sum the time and BeforeStart rows above itself, if it doesn't then it is 0.

m dax powerquery
2 Answers

In powerquery, try below and replace Table2 with the name of your source data table

let 
  xFunction = (xTable as table) as table => let
  #"Added Index" = Table.AddIndexColumn(xTable, "Index", 1, 1, Int64.Type),
  #"Added Custom" = Table.AddColumn(#"Added Index", "Running Total", each List.Sum(List.FirstN(#"Added Index"[Time],[Index]-1)))
  in  #"Added Custom",

Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Time", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"data", each  xFunction(_), type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Time", "Running Total"}, {"Time", "Running Total"})
in #"Expanded data"

enter image description here

user_92642300

Try this function:

=IF(COUNTIF($A2:$A$2,$A2)=1,0,SUMPRODUCT((A2:$A$2=A2)*B2:$B$2)-B2)
user_129380370
Related