Cumulative Sum with Two Dimensions in a Pivot Table

[mjperreault]

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

[mjperreault]

@sunny_talwar ,

Was wondering if you had any thoughts.

Thanks,
Mark

[sunny_talwar · Accepted solution]

See if this works for you

RangeSum(Sum(Sales), Before(TOTAL Sum(Sales), 1, ColumnNo(TOTAL)))

[mjperreault]

Perfect thank you!!

[BabyyGurusamy]

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.