Excel 2007/2010 Pivot Table - It's just a number

[dean12]

Working with Pivot tables today. The data is simple:

Category
Hours

So we might have:

Category      Hrs
Work           8
Work           8
Vacation       8

We want a pivot table with rows being the Category and two columns Hrs and % of total Hrs. We get

Category       Sum Hrs    %
Work             16       66
Vacation          8       33

OK, that's all easy - no problems. Now here's the zinger. We want a "goal". Say our work must be 70% (our goal) and Vacation 30%. So what I want is a Pivot table that looks like:

Category       Sum Hrs    %     Goal
Work             16       66     70
Vacation          8       33     30

So how to pull this off? I tried a calculated field with something like VLOOKUP but I can't seem to make that work. So then I thought maybe I could put the Goal number in the dataset. Something like:

Category      Hrs   Goal
Work           8     70
Work           8     70
Vacation       8     30

We can't just SUM because we would get:

Category       Sum Hrs    %     Goal
Work             16       66    140
Vacation          8       33     30

Brilliant me I decided to do MAX rather than SUM and bingo:

Category       Sum Hrs    %     Goal
Work             16       66     70
Vacation          8       33     30

But the downside is that summary figures are calculated using MAX and that's no good. A more complex example shows this. Here's the Pivot table with more data

Category            Sum Hrs    %     Goal   Correct Goal Is
Customer Facing       24       60     60     70
     Work             16       40     60     60
     QA testing        8       20     10     10
Mon-Customer Facing   16       40     20     30
     Vacation          8       20     20     20
     Sick Time         8       20     10     10
Grand total           40      100     60    100

so what happens is the MAX function is applied to the sub-totals and the grand total. That's no good.

So anyone got any good ideas on how to do this? I'm fine with calculated fields, fine with data in the dataset but I getta get the sub-totals and totals right.

Thanks.

 

[SkipVought]

hi,

When I have complex summary requirements, like you have posted, I do not use a PT.

Rather use aggregation formulas, like SUMIFS() or COUNTIFS() or SUMPRODUCT(). You will have much more control.

BTW, alos make surre that your source table is a Structured Table. The formula construction with ST, can be a piece of cake. One of the GREAT improvements in Excel @ 2007+

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

 

[xlbo]

Your real problem here is having multiple lines attriburted to "Work" in your 1st example

If there is a good reason for this, I would simply create an interim data set that sums up your hours by category 1st. At this level you can then put your goal and do a pivot table off that

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dean12]

Yea, sadly Pivot tables let you add information 8 ways from Sunday so summarization before hand is not possible. Geez, its amazing how limited Pivot tables can be.

 

[xlbo]

they're actually incredibly powerful but they rely on having a normalised data set powering them

Your other option is to set the source of the pivot table to be an internal query within the workbook - so use tyhe get external dat aoption but rather than point to an external source, point to your own workbook - you can then pre-summarise in the query that feeds the PT

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question