Creating a Calculated Field From a Running Total Field

[Joe]

I currently have a Pivot Table that does a Running Total of Net Activity over
Time using Month as my Base Field.

Month Jan Feb Mar
Activity 5 4 2
Running Total 5 9 11

I need to be able to create and additional Calculated Field (Running Total -
Activity) using one of the Pivot Table Fields and the Running Total Field.

Month Jan Feb Mar
Activity 5 4 2
Running Total 5 9 11
New Calc Field 0 5 9

Once I create the Running Total it is not available as one of my field
choices in the Insert Calculated Field option. Please help. This comes up
often as one of our sales measures (Beginning of Month Activity) I currently
have to calculate this in the data warehouse however I would like to save
resources and effort by doing it here as I have the peices I need.

Thanks...

 

[smartin]

I currently have a Pivot Table that does a Running Total of Net Activity over
Time using Month as my Base Field.

Month Jan Feb Mar
Activity 5 4 2
Running Total 5 9 11

I need to be able to create and additional Calculated Field (Running Total -
Activity) using one of the Pivot Table Fields and the Running Total Field.

Month Jan Feb Mar
Activity 5 4 2
Running Total 5 9 11
New Calc Field 0 5 9

Once I create the Running Total it is not available as one of my field
choices in the Insert Calculated Field option. Please help. This comes up
often as one of our sales measures (Beginning of Month Activity) I currently
have to calculate this in the data warehouse however I would like to save
resources and effort by doing it here as I have the peices I need.

Thanks...

Hi Joe,

This seems a little circular. Unless I misunderstand, you are just
looking for the running total from the prior month, which you have
already calculated (just in a different month).

Also, not sure if you were aware or if this helps, but you can make the
pivot table do running sums without calculated fields. To do this, right
click the data field, Field Settings | Options | Show data as | Running
Total in | Base field = Month

 

[Joe]

Thanks for the reply. I am actually using a running total based on month in
the pivot table. What I want to do is to be able to create a calculated
field using a pivot table field in combination with my running total field.

Joe

 

[smartin]

Hi Joe,

I do not know a pivot table solution for this, but I do find sometimes
taking the data "offline" from the pivot is a way to get the job done.
One simple way is just to reference the cells that are in the pivot in a
separate worksheet. Another way (a little fancier) is to leverage the
GETPIVOTDATA function. Hope this gives you some ideas.