Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Using Before to create a running total

I'm having some problems with the Before function.  Here is a simplified version of what I'm trying to achieve:

Year201220122012201320132013
MonthOctoberNovemberDecemberJanuaryFeburaryMarch
A244324
B564223
C823622
Running Total1527 (12 + 15)38 (27 + 11)49 (38 + 11)55 (49 + 6)64 (55 + 9)

This is my expression:

rangesum ( sum ( A + B + C) , Before ( total ( sum ( A + B + C ), 1, ColumnNo() ) )

But this is what I am getting:

Year201220122012201320132013
MonthOctoberNovemberDecemberJanuaryFeburaryMarch
A244324
B564223
C823622
Running Total1527 (12 + 15)38 (27 + 11)22 (11 + 11)28 (22 + 6)37 (28 + 9)

The problem being that in January instead of Before using the Running Total for December it uses the Total for December.

I know this is because "If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current column in all dimension rows except for the row showing the last horizontal dimension of the inter field sort order. The inter field sort order for horizontal dimensions in pivot tables is defined simply by the order of the dimensions from top to bottom."

But what would be the expression be to calculate the Running Total as I would like?

Many thanks for any help,

Matt

1 Solution

Accepted Solutions
MVP

rangesum ( sum ( A + B + C) , Before ( total ( sum ( A + B + C ), 1, ColumnNo(TOTAL) ) )

2 Replies
MVP