Memory efficient clustered running total in Power BI

Today I want to share a scenario where a running total calculation in the query editor saved a model that run out of memory when done with DAX:


The model couldn’t be refreshed and returned out of memory error with a calculated column in the fact table of over 20 Mio rows (from a csv-file). A running total should be calculated for each “JourneyID”, of which there were over 1 Mio in the table itself. This rose memory consumption during refresh by over 300 % – until it finally errored out:

Besetzung =
    SUM ( Fact[Entries] )
– SUM ( Fact[Exits] );
    FILTER (
        ALLEXCEPT ( Fact; Fact[JourneyID] );
<= EARLIER ( Fact[StopId] )


In the query editor, I grouped the fact-table by “JourneyID” and choose “All Rows”:

Group in the query editor to effectively partition your Fact-Table

Due to the fact that the fact table was sorted by the “JourneyID”, I could use GroupKind.Local and manually tweaked the resulting code like so :

Table.Group(#"Changed Type2", {"JourneyID"}, {{"All", each _, type table}}, GroupKind.Local)

Without this option, the calculation would have also errored out.

This effectively created table partitions, with one row for each “JourneyID”:

One table per JourneyID

I then added a custom column where I used my superfast M-code for running totals, referencing the column “All” with the partition in it:

Table.AddColumn(#"Grouped Rows", "Custom", each fnRT([All], "JourneyID", “Movements”))

Function Code

This M-code for running totals is amazingly fast (within M-dimensions 😉 ) and particularly plays its strengths on large sections for aggregation.


Memory consumption rose from around 1,700 MB to 2,000 MB, all very stable. Of course, refresh duration rose (by 150 % for that table). But for that use case, it was an acceptable price to pay.

Credits to the core-function (using List.Generate) go to Bill Szysz.

Enjoy & stay queryious 😉

Comments (22) Write a comment

  1. Could you please provide your “big” source file for me to observe the efficiency improvement as mentioned.


  2. Pingback: Running Totals In Power BI With M – Curated SQL

  3. Hi,

    a note about your DAX formula:

    Unless, they changed it, there is a difference between
    ; FILTER ( ALLEXCEPT ( Fact; Fact[JourneyID] )
    ; TRUE


    ; ALLEXCEPT ( Fact; Fact[JourneyID] )

    The first one returns the whole Fact table (minus the [JourneyID] column), filters it and then adds it to the context filter.

    The second one removes any filter on the Fact table, except those applied to [JourneyID].

    I suspect the following should run much faster than your original calculation:
    Besetzung =
    CALCULATE ( SUM ( Fact[Entries] ) – SUM ( Fact[Exits] );
    ALLEXCEPT ( Fact; Fact[JourneyID] );
    Fact[StopId]<= EARLIER ( Fact[StopId] )



    • Thanks for this code!!

      Although it runs a bit faster, the problem with the memory consumption still continues.
      So at least for my model, I have to stick to the M-solution, as this doesn’t spike the RAM-consumption.


  4. Pingback: Optimizing M Function Calls With Function.ScaleVector() – Curated SQL

  5. Pingback: Writing data to GitHub using Power Query only – The BIccountant

  6. Hello Imke,

    I’m new in power query / power bi. I tried to recreate your code but stuck to create your function. Because I didn’t understand it, could it possible to share a sample file (not with the real data of course)?

    have nice day


  7. This is wonderful and so helpful! Thank you! I was able to execute using your code and it is generating a running total, but it’s not sorting correctly before calculating the running total. Do you have any suggestions as to what I may have missed in my use of your code?


    • There is a Table.Buffer-command in the sort-step. Missing that might cause such a thing.
      And of course referencing the wrong column in the 2nd argument of the function.


  8. Hello Imke,

    I really love what you have done. Unfortunately I could not find the Table.Buffer-command and could fix this myself. Could you share this with the community?

    Many thanks in advance,



    • Hi ferrisstate,
      the buffer is in row 16 of the function code above.
      Cheers, Imke


  9. Dear imke

    I would like to use tyour superfast M-code for running total, but I need know if your code modfied with more columns
    So I get running total per group I have the following columns

    Col1 = Site ( in total 3 sites)
    Col2 = Warehouse (in total 12 warehouses)
    Col4 -= Item ID (About 1,000 items)
    Col5 = Week number (3 months horizon )
    Col5 = Index ( Transaction Index is create by group Site, Warehouse, Item ID, weeknumber)


    • Sorry I was typing to fast
      I would like to use your superfast M-code for running total, but I need to know if your code can be modfied with additional columns, so I get running total per group. If yes, could you please provide an code example of how to do?

      I have the following columns and want a running total per Site, Warehouse, Item ID and weeknumber

      Col1 = Site (In total 3 sites)
      Col2 = Warehouse (In total 12 warehouses)
      Col4 = Item ID (About 1,000 items)
      Col5 = Week number (3 months horizon )
      Col5 = Index ( Transaction Index is created by grouping: Site, Warehouse, Item ID and weeknumber)
      Col6 = Quantities


      • Hi Jan,
        you should group on all these columns with Operation “All Rows”.
        That will create one table per group combinations. Run the function on those “partitions”.
        That’s the fastest method there is.


    • Hi Jan,
      sorry, I missed your comment.
      You can include multiple columns as group columns: Just switch from “Basic” to “Advanced” in the UI and add Col1-4 to it.
      Week number and Index columns should show up, although you’d have to rename the “Index” column because it will conflict with the default column names of what the function returns.


  10. Hi Imke

    Now I have tried to replicate your case and grouped all rows so I end up with a table with 2 column
    Column 1 = WeekNumber (Sorted in ascending Order)
    Column 2 = All (partition/nested tables)

    Table= [All]
    AmountColumn= “Qty” ( Qty Column inside the [All] partitions table)

    Your code cant find “AmountColumn” with the below M-code

    // Select the Columns
    SelectColumns = Table.SelectColumns(Sorted, {SortColumn, AmountColumn, “Index”}),


  11. Dear Imke

    After long time of investigation of above function I found a your 2nd. solution of RT from this website

    Your function below is supporting table partitions (grouped table) 🙂

    func = (Table as table, AmountColumn as text, optional SortColumn as text, optional GroupColumns) =>
    Debug parameters*/
    Table = Grouped,
    SortColumn = “Country”,
    AmountColumn = “Value”,
    GroupColumns = {“Country”},

    GroupedTable = Table.Group(Table, GroupColumns, {{"Partition", each _, type table}}),
    fnRunningTotal = (Table as table, AmountColumn as text, optional SortColumn as text, optional GroupColumns) =>
    Source = if SortColumn = null then Table else Table.Buffer(Table.Sort(Table,{{SortColumn, Order.Ascending}})),
    // Sort table and buffer it
    Buffered = Table.Buffer(Table.AddIndexColumn(Source, "Index",1,1)),
    // Select the Columns
    SelectColumns = Table.SelectColumns(Buffered, if SortColumn = null then {AmountColumn, "Index"} else {SortColumn, AmountColumn, "Index"}),
    // Extract Amount column and buffer it
    ExtractAmountColumn = List.Buffer(Table.Column(SelectColumns, AmountColumn)),
    // Calculate a list with all running Totals
    RunningTotal = List.Skip(List.Generate( ()=> [ListItem=0, Counter=0]
    ,each [Counter] <= List.Count(ExtractAmountColumn)
    ,each [ ListItem = ExtractAmountColumn{[Counter]}+[ListItem],
    Counter = [Counter]+1
    ConvertedTable = Table.FromList(RunningTotal, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedColumn = Table.ExpandRecordColumn( ConvertedTable, "Column1", {"ListItem", "Counter"}, {"ListItem", "Counter"}),
    MergedQueries = Table.NestedJoin(Buffered,{"Index"}, ExpandedColumn,{"Counter"},"Expanded Column1",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn( MergedQueries, "Expanded Column1", {"ListItem"}, {"RunningTotal"}),
    #"Removed Columns" = Table.RemoveColumns(Expand,{"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"RunningTotal", type number}})
    #"Changed Type",
    AddRTToGroup = Table.AddColumn(GroupedTable, "Custom", each fnRunningTotal([Partition], AmountColumn)),
    #"Removed Other Columns" = Table.SelectColumns(AddRTToGroup,{"Custom"}),
    ExpandedGroup = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", Table.ColumnNames(Table) & {"RunningTotal"}),
    WithoutGroup = fnRunningTotal(Table, "Value"),
    Result = if GroupColumns = null then WithoutGroup else ExpandedGroup

    documentation = [ Documentation.Name = ” Table.ColumnRunningTotal”
    ,Documentation.Description = ” Fast way to add running total to a table”
    ,Documentation.LongDescription = ” Fast way to add running total to a table”
    ,Documentation.Category = ” Table”
    ,Documentation.Source = ” local”
    ,Documentation.Author = ” Imke Feldmann:
    ,Documentation.Examples = {[Description = ” “, Code = ” “, Result = ” “]}]
    Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))*/
    For your table, you would call it like so: 
    MyFunction(YourTable, “Value”, null, {“Name”})


    • Hi Jan,
      glad to hear you found it useful and thanks for sharing your adoption.


Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: