Announcements
cancel
Showing results for
Did you mean:
Creator

## Cumulative Sum with Two Dimensions in a Pivot Table

Hi All,

I have data structured as the below

 Weekstart Day Sales 3/30/2020 3/30/2020 6,621,339 3/30/2020 3/31/2020 25,754,389 3/30/2020 4/01/2020 316,020,608 3/30/2020 4/02/2020 4,591,320 3/30/2020 4/03/2020 1,504,342 3/30/2020 4/04/2020 1,485,091 4/5/2020 4/05/2020 1,377,793 4/5/2020 4/06/2020 4,795,397 4/5/2020 4/07/2020 1,128,845 4/5/2020 4/08/2020 1,857,699 4/5/2020 4/09/2020 1,419,651 4/5/2020 4/10/2020 5,436,237 4/5/2020 4/11/2020 14,677,500

On my sheet I want to create a Pivot Table with the Dimensions Weekstart and Day as columns and the measures sum(Sales) and "Cumulative Sales" as rows.  For "Cumulative Sales" I am using the formula

Rangesum(Sum(Sales),Before(Sum(Sales),1,ColumnNo()))

This is working fine when I have the Weekstart dimension fully collapsed

However when I expand one of the Weekstart's then Cumulative works for the days within that week but does not work for the next Weekstart.  In the below I would expect the value for 4/5/2020 to still be 386,670,214 not 30,363,124

Does anyone have any ideas on how to accomplish this?

I've attached the source data and QVF if anyone wants to take a look

Thanks!
Mark

Labels (4)

• ### RangeSum()

1 Solution

Accepted Solutions
MVP

See if this works for you

``RangeSum(Sum(Sales), Before(TOTAL Sum(Sales), 1, ColumnNo(TOTAL)))``
4 Replies
Creator
Author

Was wondering if you had any thoughts.

Thanks,
Mark

MVP

See if this works for you

``RangeSum(Sum(Sales), Before(TOTAL Sum(Sales), 1, ColumnNo(TOTAL)))``
Creator
Author

Perfect thank you!!

Contributor

I have a pivot table with 3 dimensions (Year, Quarter, Month) and calculating cumulative sum of sales using below expression
RangeSum( Above(TOTAL Sum(Sales), 1, Rowno(TOTAL)))

Cumulative sum is working fine when dimensions are fully expanded and not giving correct result when its collapsed.