Calculations using Pivot Table data...

[ExcelUser64]

I'm a reasonably new user of Pivot Tables, but appear to have stumped other experts at work with this problem. I have the following sample data in the first three columns and the Pivot Table to the right of it as follows:

[TABLE="width: 656"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>[/TABLE]
[TABLE="width: 605"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Task[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD]Count of Task[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Task 1[/TD]
[TD]Done[/TD]
[TD][/TD]
[TD]Row Labels[/TD]
[TD]Done[/TD]
[TD]Miss[/TD]
[TD]WIP[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]Task 2[/TD]
[TD]Miss[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]Task 3[/TD]
[TD]Miss[/TD]
[TD][/TD]
[TD]Luke[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Task 4[/TD]
[TD]Done[/TD]
[TD][/TD]
[TD]Chris[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Task 5[/TD]
[TD]Done[/TD]
[TD][/TD]
[TD]Tom[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]Task 6[/TD]
[TD]WIP[/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]Task 7[/TD]
[TD]WIP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Task 8[/TD]
[TD]Miss[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Task 9[/TD]
[TD]Miss[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]Task 10[/TD]
[TD]Done[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]Task 11[/TD]
[TD]Miss[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Task 12[/TD]
[TD]WIP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I need to do is add the number of tasks that were either Missed or WIP and divide that number by the Grand Total. Near as I can tell, this means that for each user, I need to somehow 'identify' the data that I want to use for the above calculation from within the Pivot Table.

So, how do I pick the appropriate data, add it, and then divide by another value? This, of course, would go into a new calculated field within the Pivot Table (say, "IncompleteWork").

Thanks!

 

[ExcelUser64]

One final question. Is there a way to show just the PPC colums as a percentage of the total and leave the others as raw data?

 

[RoryA]

Not really, unless you have Power Pivot. You could simply duplicate the fields and then hide the columns for the ones you don't want visible, though that's messy.

 

[ExcelUser64]

Really? You can duplicate the fields in the Pivot Table?
How do I place a second instance of 'Count of Task' in the Pivot Table's 'Values' field?

 

[RoryA]

Just drag the field in again.

 

[ExcelUser64]

Tried that - I had to delete the 'Calculated Item' first, then could drag in a second copy. However, when I then tried to re-add the Calculated Item, it wouldn't do it (Multiple Data Fields of the same field aren't supported with Calculated Items). Oh well.

Fortunately, the prior advice gets me what I need - so thanks again!

 

[RoryA]

Ah yes! That's actually one of the reasons that calculated items are rarely useful, so I should have remembered that really!