Power pivot running total

Dicken 41 Reputation points
2022-01-22T15:56:53.107+00:00

Hi,
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,669 questions
{count} votes

Accepted answer
  1. Lz._ 8,991 Reputation points
    2022-01-25T18:09:19.787+00:00

    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:

    =SUMX(FILTER(ZCAR,ZCAR[Index]<=EARLIER(ZCAR[Index])),ZCAR[Value])  
    

    168438-powerpivotruntotalcolumn.png

    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Dicken 41 Reputation points
    2022-01-24T14:30:53.137+00:00

    Hi,
    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.

    RD

    0 comments No comments

  2. Dicken 41 Reputation points
    2022-01-26T13:53:20.423+00:00

    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.

    RD

    0 comments No comments

  3. Dicken 41 Reputation points
    2022-01-27T14:28:17.33+00:00

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

    CALCULATE(SUM(Table1[Unit]),Table1[Index]<=EARLIER(Table1[Index]))

    RD