CODEWITHSUNDEEP
excelpivot-tableaccumulate
Cenderze
Cenderze

Reputation: 1222

Using Rolling Sum on calculated fields, Pivot Table in Excel

I have a column called Sales which I need to find the change between current month's and previous month's sales.

For this I've tried:

2016 Sales      MonthlyChange   
jan 15401168    
feb 14562410    -838758
mar 13914329    -648081
apr 14471132    556803
maj 13471177    -999955
jun 13207358    -263819
jul 13423816    216458
aug 12322774    -1101042
sep 12109737    -213037
okt 11905915    -203822
nov 12910189    1004274
dec 15136118    2225929

I also want to find the accumulated change over each month. 

2016   Sales   MonthlyChange   Acc change
jan 15401168            -838758
feb 14562410    -838758 -1486839
mar 13914329    -648081 -91278
apr 14471132    556803  -443152
maj 13471177    -999955 -1263774
jun 13207358    -263819 -47361
jul 13423816    216458  -884584
aug 12322774    -1101042    -1314079
sep 12109737    -213037 -416859
okt 11905915    -203822 800452
nov 12910189    1004274 3230203
dec 15136118    2225929 2225929

MonthlyChange is created using Show value as -> Difference -> (previous)month. Is it possible to either:

1) Use the MonthlyChange as a Value field, insert it to my Pivot Table and conduct a Show values as -> Running sum? I haven't found any option to do this

2) Conduct two calculations subsequently on the same column in one PivotTable?

EDIT

Is it also possible to have the MonthlyChange's first value to correspond to the actual value of jan, i.e. 15401168 instead of a blank value?

Upvotes: 0

Views: 3555

Answers (1)

DMM
DMM

Reputation: 1112

See comments also.

Arithmetically, cumulative change is simply the difference from the first month jan. So your "Accumulated Change" column can be defined changing the (previous) selection you have previously used in the Value Field Settings dialog to jan.

You can also drag a field (such as Sales) multiple times from the Pivot Table Field List to the Values area. Each occurrence can be displayed differently by setting its display characteristics differently through the Value Field Settings dialog.

enter image description here

In the picture, the Sales field has been dragged 3 times into the Values area. The first column in the body of the pivot table , labelled Sum of Sales, is just simple a summation of the input values (which simply reproduces the input data because the source data contains only one Sales value for each Month). The second column (labelled Sum of Sales2) is the difference from (previous) as described in the question and the third column (Sum of Sales3) is the cumulative change (described above).

The labels for the 3 columns in the values area of the Pivot table are the defaults provided by Excel and can be changed into something more meaningful using the Value Field Settings dialog

Upvotes: 2

Related Questions