Pivot Table multiple grand totals
 

How can I create multiple grand totals in a Pivot Table in Excel 2007? For
instance, one Sum and one Average. I want to be able to see the total for
the column and also the average for the column. Is this possible on the same
table or do I have to make two seperate tables?

Pivot Table multiple grand totals
 

Hi,

Just add the calculated field to the VALUES area twice. Then change one of
the calculation to average using the Field Setting option.

I would give you a more specific answer if you showed us a sample of your
data.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"RoofIL" wrote:

How can I create multiple grand totals in a Pivot Table in Excel 2007? For
instance, one Sum and one Average. I want to be able to see the total for
the column and also the average for the column. Is this possible on the same
table or do I have to make two seperate tables?

Pivot Table multiple grand totals
 

I see what you mean, but can it be done another way so that I don't have
twice the columns?

Data:

Project Labor Material Disposal

A Res. 10 50 10
B Res. 15 60 15

G. Total 25 110 25


I would like it to be:

Project Labor Material Disposal

A Res. 10 50 10
B Res. 15 60 15

G. Total 25 110 25
Average 12.5 55 12.5


"Shane Devenshire" wrote:

Hi,

Just add the calculated field to the VALUES area twice. Then change one of
the calculation to average using the Field Setting option.

I would give you a more specific answer if you showed us a sample of your
data.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"RoofIL" wrote:

How can I create multiple grand totals in a Pivot Table in Excel 2007? For
instance, one Sum and one Average. I want to be able to see the total for
the column and also the average for the column. Is this possible on the same
table or do I have to make two seperate tables?

Pivot Table multiple grand totals
 

Hi

Regrettably that's the way it works.
If you Pulled Your Data button back from Total, so Average was appearing
under Sum, you would get double the number of Rows.

However, with that layout, you could put the following Event code on your PT
sheet, and that would hide the Average Rows apart from the Total Average.

Change the Const Colno in the Code to the Column Number where your Sum of
Value and Sum of Average labels appears.

Private Sub Worksheet_Activate()
Dim c As Range
Application.ScreenUpdating = False
PivotTables(1).PivotCache.Refresh
Cells.Select
Rows.Hidden = False
Const Colno = 7 '<=== Change to suit your Column
For Each c In Intersect(UsedRange, Columns(Colno))
If UCase(Left(c, 7)) = "AVERAGE" Then
c.Rows.EntireRow.Hidden = True
End If
Next
Cells(1, Colno).Select
Application.ScreenUpdating = True

End Sub

Copy the Code above
Right click Sheet tab where PT exists View Code
Paste code into white pane that appears
Alt+F11 to return to Excel

This is event code which will be triggered automatically.

--
Regards
Roger Govier

"RoofIL" wrote in message
...
I see what you mean, but can it be done another way so that I don't have
twice the columns?

Data:

Project Labor Material Disposal

A Res. 10 50 10
B Res. 15 60 15

G. Total 25 110 25


I would like it to be:

Project Labor Material Disposal

A Res. 10 50 10
B Res. 15 60 15

G. Total 25 110 25
Average 12.5 55 12.5


"Shane Devenshire" wrote:

Hi,

Just add the calculated field to the VALUES area twice. Then change one
of
the calculation to average using the Field Setting option.

I would give you a more specific answer if you showed us a sample of your
data.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"RoofIL" wrote:

How can I create multiple grand totals in a Pivot Table in Excel 2007?
For
instance, one Sum and one Average. I want to be able to see the total
for
the column and also the average for the column. Is this possible on
the same
table or do I have to make two seperate tables?

Pivot Table multiple grand totals
 

Excel 2007
A regular average would work if Project items
did not repeat in the PT data source.
A sum and average subtotal for a dummy group would work for that.
Otherwise use helper columns or external tables:
http://www.mediafire.com/file/zqzomt...03_16_09a.xlsx