Calling all Excel PivotTable Gurus

September 14, 2016 8:07 AM

How do I construct this PivotTable to show what I need?

So, first things first, some sample data:

GROUP          SECTION          TEAM          EMPLOYEE          EXCEEDS GOAL
North          Apple            Red           Bob               Yes
North          Apple            Red           Jane              No
North          Pear             Yellow        Trudy             No
North          Pear             Yellow        Randy             No
Central        Potato           Purple        Harriet           Yes
Central        Potato           Violet        Jenny             Yes
Central        Carrot           Orange        Jim               Yes
Central        Carrot           Orange        Frank             No

I would like to, simply, see the percentage of persons exceeding goal, at all three grouping levels, GROUP, SECTION and TEAM, on one pivot table.

To explain more fully, at first glance I want the pivot table to show two columns: GROUP and % EXCEEDS GOAL, with the % EXCEEDS GOAL being the % that are Yes. So at this level it would have two rows, North and Central, showing 25% and 75% respectively.

Then, I'd like to be able to expand the GROUP to see the next grouping level, SECTION, and how many persons exceeds goal in that section. So this would show under North that Apple is at 50% and Pear is at 0%, and under Central that Potato is at 100% and Carrot is at 50%.

Lastly, I need to expand GROUP to see the percentages at the TEAM level.

I'm really not too shabby at this pivot table crap but after 45 minutes of trying to figure this out I'm calling for reinforcements.

Is this possible? I think the issue might be that I need to stage the data in a different way. If that's the case I'm totally willing to doing so. Please lead me to the promised land!

Thanks in advance for the help!

posted by bologna on wry to Computers & Internet (3 answers total)

Best answer: Not only possible, but easy!

Drag, in order, "Group", "Section", and "Team" to the row labels box. You should be able to Expand and and close these as you want by clicking the little minus signs in the pivot table.

Next, Drag "Exceeds Goal" to Column Labels, then also drag "Exceeds Goal" to Values.

You'll now have a count of exceeds goal by Yes or No. Now go over to your pivot table and right click on one of the exceeds goals values. Go to "show values as" and select "% of Row total".

The only thing this does that you don't want is it will also give you a % of "No", but that's probably acceptable? You can turn off the "grand total" column (which will always be 100%) by going to the PivotTable Design Tab, selecting Grand Totals, and saying "On for Columns Only". And/Or you can just hide the "No" column by right clicking it's column letter and saying "hide column"

Does that do what you want it to do?
posted by brainmouse at 8:16 AM on September 14, 2016

Response by poster: Well damn, that was quite embarrassingly simple. I had done it 80 ways til Sunday, and I apparently didn't try bringing the Exceeds Goal down as column AND value; that solved it! Thanks so much for the quick help!
posted by bologna on wry at 8:38 AM on September 14, 2016

If you can live with replacing the Yes/No values with 1/0 (numerical) values, then the average of these values would give you the "% met" at any level of grouping you would like.

This is faster to set up, and gives you the same answer as using all of those more complicated Pivot options.
posted by jason6 at 8:38 AM on September 14, 2016