Pivot Table Grand Total Plus Calc Field or GT Running Balance

[Mathew P Bennett]

Good Morning All, XL2007
I have a basic Pivot Table summing vales across a row of months.

A B C D
Category Month1 Month2 Month3
Overheads 10 10 10
Expenses 20 20 20
Grand Total 30 30 30

Now I wish to insert another Grand Total Row which will show
the Running Balance ie

A B C D
Category Month1 Month2 Month3
Overheads 10 10 10
Expenses 20 20 20
Grand Total 30 30 30
Grand Total(2) 30 60 90

I know I could add a seperate row outside the Pivot Tabe to reflect the
Running Total, but would rather keep it included, via a
Calculated Field or such like.
I have managed to contrive a Calculated field, but this returns Subtotal
rows for the Running Balance for all my Subdivisions, eg Overheads, Expenses
etc.
I wish just the Grand Total(2) to show the Running Balance.

Is there a way?

As usual thank you for any input.
Cheers
Mathew

 

[ShaneDevenshire]

This requires a number of steps (Assume that the name of the field in the
column area is Months and the data field is Dollars:

1. Right-click in the Data area and choose Field Settings, Options, Running
Totals In, and pick the Months field and click OK twice. This will replace
the data area and totals with running totals.
2. Drag the Dollars field into the Data area a second time. You wil now
have a Sum of Dollars2 field.
3. Click the lable (row area) for one of the Sum Of Dollars2, place your
mouse along the edge of the selected cell and drag it up 1 row. This will
put the regular sums on top row and the running totals on the row below that.
4. Hide the running totals in the data area but leave it displayed in the
Totals area.

Cheers,
Shane Devenshire

 

[Mathew P Bennett]

Cheers Shane,
Haven't tried yet, but thank you for suggestion - first viable one yet,
enjoy your rest of day at work! Mine finished (UK)- dinner now! Mrs cooking!
Cheers again, I will post again, once tried out. Mathew

 

[Mathew P Bennett]

Sorry Shane, cannot get to work, do not get the Sum of Dollar2 field to
show.
Mathew

 

[Mathew P Bennett]

Shane, cheers, got it to work. Magic. Have to pul from data firld - not from
PT- Cheers Mathew

 

[ShaneDevenshire]

Hi Mathew,

Am working, but glad I could help.

FYI you should rate the answer so that others know your question was
answered.

 

[Mathew P Bennett]

Hi again, nearly there but no banana, will persevere. I think I may need
some VBA, bu at least you have given me a lead about introducing the data
feilds more than once.
Cheers M