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:

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>

Name	Task	Status		Count of Task	Column Labels
John	Task 1	Done		Row Labels	Done	Miss	WIP	Grand Total
Chris	Task 2	Miss		John	2	1		3
Luke	Task 3	Miss		Luke		2	1	3
Tom	Task 4	Done		Chris	1	1	1	3
John	Task 5	Done		Tom	1	1	1	3
Chris	Task 6	WIP		Grand Total	4	5	3	12
Luke	Task 7	WIP
Tom	Task 8	Miss
John	Task 9	Miss
Chris	Task 10	Done
Luke	Task 11	Miss
Tom	Task 12	WIP

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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!