Power pivot running total

Dicken 41 Reputation points

Is it possible to add a running total column to a table in power pivot data model, not when loaded as a pivot chart.

so in a normal table you'd have something like ; SUM(ZCAR[[#Headers],[Value]]:[@Value]) ?

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,362 questions
{count} votes

Accepted answer
  1. Lz._ 8,806 Reputation points

    Hi @Dicken

    To do this you must have an Index (1,2,3...) column in your ZCAR Table. Power Pivot cannot index a Table so you must:

    • Remove the table from the Data Model
    • Load it to Power Query. Within Excel: Data tab > Get & Transform > From Table/Range)
    • Go the Add Column tab > Index Column (you can start at any number)
    • File > Close & Load To... > check Only Create Connection + Add this data to the Data Model > OK

    Assuming the figures are in column [Value] the formula for the Running Total is:



3 additional answers

Sort by: Most helpful
  1. Dicken 41 Reputation points

    it's a bit difficult without being able to attach anything, Simply if I have 2 columns in power pivot data model,
    not loaded to power pivot table, and one of these columns contains figures, I want to add a third column containing a running total of those figures, the formula in my original question shows what I want, but this only works for excel tables.


  2. Dicken 41 Reputation points

    Thanks Lz,

    that's exactly what I was looking for, admittedly I just copied and pasted it in and changed names for columns , tables etc. I now have the job of understanding. I have used Filter a bit but not Earlier. But will be experimenting soon.


  3. Dicken 41 Reputation points

    Further to the help I received using SUMX, after some experimenting the same can be achieved by wrapping in the Calculate function,