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]

FYI - the desired result would indicate that John has 33%, Luke has 100%, Chris has 67%, and Tom has 67% - and the total project would have 8/12 = 67% of work left to be finished.

 

[RoryA]

Welcome to the forum.

You could add a calculated Item (not Field) that adds Miss and WIP together. Then filter out the individual Miss and WIP items and choose to display the counts as a percentage of the row total.

 

[ExcelUser64]

Are you suggesting that I add this calculated Item 'outside' of the Pivot Table? I did this, but then if I filter by user and one of the items (Miss, Done, or WIP) is missing from that user, the columns change (i.e. the column won't show if there's no data for that user) and the external formula 'breaks'.

Being able to specifically identify (pick) the data within the Pivot Table and perform calculations within the Table would resolve that problem.

 

[ExcelUser64]

Upon re-reading you post - how do I add the two 'Pivot Table Calculated' values together within the table?
Or are you suggesting that I do this in the original data set (which won't work as well for me)?

 

[RoryA]

No, I mean in the pivot table. Select one of the item column header cells in the pivot table, then choose to add a Calculated Item in the same dialog you use to create Calculated Fields.

 

[ExcelUser64]

Got it! I added that Calculated Item (called Test) which adds Miss+WIP (thanks!.

[TABLE="width: 403"]
<tbody>[TR]
[TD]Count of Task[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Done[/TD]
[TD]Miss[/TD]
[TD]WIP[/TD]
[TD]Test[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

However, the Test item is now added into the Grand Total. So, instead of Row 1 being 33%, it now shows 50% (Miss+WIP+Test)/Total. As such, Tom shows 80% (=4/5) instead of 67% (i.e. (1 Miss + 1 WIP)/3 Tasks ).

[TABLE="width: 472"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

As I said, you need to then filter out the Miss and WIP items so they aren't double counted.

 

[ExcelUser64]

I must have done something wrong when I filtered the first time because it didn't work. However, I tried it again and viola (renamed test to PPC)!

Thank you so much!

[TABLE="width: 358"]
<tbody>[TR]
[TD]Count of Task[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Done[/TD]
[TD]PPC[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]66.67%[/TD]
[TD]33.33%[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]0.00%[/TD]
[TD]100.00%[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]33.33%[/TD]
[TD]66.67%[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]33.33%[/TD]
[TD]66.67%[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]33.33%[/TD]
[TD]66.67%[/TD]
[TD]100.00%[/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

Glad to help. It's a rare day when calculated items are actually useful, in my experience.