Calculations using Pivot Table data...

ExcelUser64

New Member
Joined
Oct 18, 2017
Messages
9
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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
As I said, you need to then filter out the Miss and WIP items so they aren't double counted.
 
Upvote 0
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]
 
Upvote 0
Glad to help. :) It's a rare day when calculated items are actually useful, in my experience. :)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top