Power pivot running total

Question

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]) ?

Answer 1

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])  

Answer 2

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

Answer 3

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

Answer 4

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