Pivot Table - Running Calculation

D

Dimitri

I wish to add a column in a pivot table that is a running calculation. That
is, it uses values from the previous row and values from the current row. Is
there any way to do this?
 
R

Roger Govier

Hi

Assuming you have two fields, Name and Amount.
You have Name as a Row field, and Amount as a Data field.
Drag the Amount Field to the Data area a second time. Double click on it
and choose Options.
In the dropdown for Show Data as, choose Running Total and select Name
as the base field.

If you want the two columns to show side by side, drag the Data button
on the PT to the Total area.
 
D

Dimitri

Thanks for this, but it only goes part of the way to solving my problem.
I need to calculate the percentage of one running sum column to another
running sum column.

Based on your example I have the 2 running sum columns. Now I need to insert
a calculated field with the following formula: RunningSum1 / RunningSum2.

The problem is that RunningSum1 and 2 are not available in the dialog box
for creating the calculated field. Only the "base" fields can be selected.

Do you know how to achieve this?
 
D

Dimitri

Thanks for this - but it only tskes me part of the way to my goal.
You have explained to me how I create 2 running sum columns I need.
Now I need to calculate runningsum 1 as a percentage of runningsum2.
The calculated field dialog box only allows you to select the "base" fields
as factors in the calculated field expression. So , how do I perform
calculations using the runningsum fields?
 
R

Roger Govier

Hi
In that case, I wouldn't use calculated fields in the Pivot Table at
all.
I would make additional columns in my base data, carrying out the
required calculations there, and then add those fields to the data area
in the PT,
 
Top