Totals for excel 2007 pivot tables with 3 layers of column labels 1

[funforus]

Hi All,

I have an excel 2007 pivot with three levels of Column labels. I am trying to get the upper most layer of column label to break down its total by the 3rd layer level. My comma-delimted data is below. In my pivot, my row label is Employee, my column labels are Data Type, Measurement, Reporting Period in that order (my boss wants it that way). I'm trying to see if there is a way to have a total for the 'Data Type' (e.g. "Non Revenue Hours Credited") broken out by the Reporting Periods (Current Period total and Prior Period Total). Right now I just get one total for each of two Data Types with no breakout. I have searched but I can't find the answer here. It's getting late, so apologies if I'm not making sense!

Thank you,
Julie


Employee,Measurement,Measure_value,Reporting Period,Data Type
Julie,Revenue,12000,Prior Period,Revenue Data
Greg,Revenue,11600,Prior Period,Revenue Data
Alisa,Revenue,0.01,Current Period,Revenue Data
Julie,Revenue,"2,000",Prior Period,Revenue Data
Greg,Revenue,15000,Prior Period,Revenue Data
Alisa,Revenue,333000,Current Period,Revenue Data
Alisa,FaceToFaceMeeting,0.33,Prior Period,Non Revenue Hours Credited
Alisa,FaceToFaceMeeting,0.33,Prior Period,Non Revenue Hours Credited
Greg,FaceToFaceMeeting,0.33,Current Period,Non Revenue Hours Credited
Greg,FaceToFaceMeeting,1.33,Current Period,Non Revenue Hours Credited
Julie,FaceToFaceMeeting,0.33,Prior Period,Non Revenue Hours Credited
Alisa,FaceToFaceMeeting,0.33,Prior Period,Non Revenue Hours Credited
Alisa,Phone call,0.33,Prior Period,Non Revenue Hours Credited
Greg,Phone call,0.33,Prior Period,Non Revenue Hours Credited
Julie,Phone call,0.33,Current Period,Non Revenue Hours Credited
Greg,Phone call,0.33,Current Period,Non Revenue Hours Credited
Julie,Phone call,0.33,Current Period,Non Revenue Hours Credited
Alisa,Review of Contract,3,Current Period,Non Revenue Hours Credited
Alisa,Review of Contract,2.2,Prior Period,Non Revenue Hours Credited
Greg,Review of Contract,3.11,Prior Period,Non Revenue Hours Credited
Julie,Review of Contract,2.33,Current Period,Non Revenue Hours Credited
Greg,Review of Contract,4.33,Current Period,Non Revenue Hours Credited

 

[SkipVought]

Please post an example of the result you want to see, based on your posted data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[funforus]

HI Skip,

I tried to type it out but it looks too messy. I hope the attachment will work. I am looking for the pivot on the tab called 'pivot i would like', specifically, I cannot get the yellow highlighted fields no matter what I seem to try with totals/subtotals on the tab 'pivot that I get'.

http://www.mediafire.com/?ybs5s4a4cb405h3

Thank you!
Julie

 

[SkipVought]

Many of us cannot download files, due to company security restrictions.

Please post your example DIRECTLY here.

Use the TT TGML Tag. If you don;t know what TGML Tags are, search for TGML on this page at the BOTTOM and follow the link.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[funforus]

OK, makes sense. The yellow highlights are the total breakout that I can't get.

Non Revenue Hours Credited Non Revenue Hours Credited Sum
FaceToFaceMeeting Phone call Review of Contract Total Total
Current Period Prior Period Current Period Prior Period Current Period Prior Period [highlight]Current Period[/highlight] [highlight]Prior Period[/highlight]
Alisa 0.00 0.99 0.00 0.33 3.00 2.20 [highlight]3.00[/highlight] [highlight]3.52[/highlight]
Greg 1.66 0.00 0.33 0.33 4.33 3.11 [highlight]6.32[/highlight] [highlight]3.44[/highlight]
Julie 0.00 0.33 0.66 0.00 2.33 0.00 [highlight]2.99[/highlight] [highlight]0.33[/highlight]

 

[SkipVought]

I could not do it with as PT.

I can with FORMULAS.

My results:
[tt]
FaceToFaceMeeting FaceToFaceMeeting Phone call Phone call Review of Contract Review of Contract
Current Period Prior Period Current PeriodPrior Period Current Period Prior Period Current Period Prior Period
Alisa 0 0.99 0 0.33 3 2.2 3 3.52
Greg 1.66 0 0.33 0.33 4.33 3.11 6.32 3.44
Julie 0 0.33 0.66 0 2.33 0 2.99 0.33
[/tt]
Table is in a new sheet starting in A1, using NAMED RANGES of the Source Data Table...
[tt]
Detail Summary:
B3: =SUMIFS(Measure_value,Employee,$A3,Measurement,B$1,Reporting_Period,B$2)
Copy across thru column J, down thru each employee

Totals:
H3: =SUMIF($B$2:$G$2,H$2,$B3:$G3)
Copy to adjacent cell, down thru each employee
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I squeezed it down for clarity
[tt]
FaceToFaceMeePhone call Phone call Review of ContReview of Contract
Current PeriodPrior Period Current PeriodPrior PeriodCurrent PeriodPrior PeriodCurrent Period Prior Period
Alisa 0 0.99 0 0.33 3 2.2 3 3.52
Greg 1.66 0 0.33 0.33 4.33 3.11 6.32 3.44
Julie 0 0.33 0.66 0 2.33 0 2.99 0.33
[/tt]
For the formulas to work, the category headings (FaceToFaceMeeting etc) must be in BOTH COLUMNS (Current & Prior)

If you want to 'pretty it up,' then HIDE those 2 ROWS and make your display heading anything you want.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[funforus]

Hi Skip,

Thanks so much for the thoughtful answer, so a star for you! Unfortunately, I have to maintain the pivot filtering they have set up for the user so I think I will have to end up putting the data in crosstab type query at its source. Thanks again though,
Julie

 

[SkipVought]

You could do the TOTAL Summations outside the PT, but you would have to modify the row range of the summations to correspond with the PT row range as filters change.

It could be done with an event driven macro, using the Worksheet_Change event.

If you want to pursue that solution, post your question in forum707 for VBA code help.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]