Pivot Table Totals When Using Pages

[Guest]

Dear fellow Excel 2003 users,

I have a pivot table that lists hours of several employees. A manager
asked if there was a way to view just the non-billable hours. No
sweat, I added a page field and selected non-billable and the sum of
hours and the grand total showed just the non-billable hours.
However, he was hoping to see the data section of the pivot table show
just the non-billable hours with a column next to it showing the
percentage of non-billable compared to the grand total of billable and
non-billable.

Problem: after scouring Google and the Help file, I cannot find a way
to keep a filtered data section (just non-billable hours) of a pivot
table while maintaining the complete grand totals (all hours - both
billable and non-billable) without copying and pasting the grand
totals as values off to the side of the pivot table.

Has anyone ever come up with a solution? Thanks in advance!

Kevin

 

[Roger Govier]

Hi

I can only do it by "cheating"
With a simple table of Name, Hours, Type (Billable or Non Billable) I
produced a PT with Name as Row, Type as Column and Hours as Data, PT
going to new sheet at A1.

On PT sheet in E4 type % Non Billable
in E5
=GETPIVOTDATA("Hours",$A$3,"Name",A5,"Type","Non
Billable")/GETPIVOTDATA("Hours",$A$3,"Name",A5)
format cell as Percentage
Copy down as Required
Hide columns B & D
Format column E to look like PT format.