Running Total (Cumulative Sum) with Categories

Hi Anonymous ,

You can get the running total by the following methods:

1. In Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUXIEYkMDpVidaCVDGNcUzDUCMp2A2AgiawzjQmRNoIqNIbKmUFljoGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S.NO = _t, ROUTE = _t, VOL = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"S.NO", Int64.Type}, {"ROUTE", type text}, {"VOL", Int64.Type}}),
    #"Groupby"= Table.Group(#"Changed Type", {"ROUTE"}, {{"GroupDetail", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded GroupDetail" = Table.ExpandTableColumn(Groupby, "GroupDetail", {"S.NO", "VOL", "Index"}, {"S.NO", "VOL", "Index"}),
  FX = (values as list, grouping as list) as list =>

let
    GRTList = List.Generate
    ( 
        ()=> [ GRT = values{0}, i = 0 ],

        each [i] < List.Count(values),

        each try 
                 if grouping{[i]} = grouping{[i] + 1}  
                 then if [GRT]>0  then  [GRT = [GRT] + values{[i] + 1}, i = [i] + 1] else [GRT =  values{[i] + 1}, i = [i] + 1] 
                 else [GRT = values{[i] + 1}, i = [i] + 1]
        
             otherwise [i = [i] + 1]
    ,
        each [GRT]
    )
in
    GRTList,

    BufferedValues = List.Buffer( #"Expanded GroupDetail" [VOL]),
    Bufferedgroup = List.Buffer( #"Expanded GroupDetail" [ROUTE]),
    Runningtotal = Table.FromColumns(
    {
     #"Expanded GroupDetail"[ROUTE],  #"Expanded GroupDetail"[S.NO], #"Expanded GroupDetail"[VOL], 
     FX(BufferedValues, Bufferedgroup)
    },
    {
      "Route",
      "SNO",
      "VOL",
      
      "Running Total"
    })
in
    Runningtotal

2. By DAX: Create a calculated column as below

Column = 
CALCULATE (
    SUM ( 'Table'[VOL] ),
    FILTER (
        'Table',
        'Table'[Route] = EARLIER ( 'Table'[Route] )
            && 'Table'[SNO] <= EARLIER ( 'Table'[SNO] )
    )
)

Best Regards