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]

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!.

Count of Task	Column Labels
Row Labels	Done	Miss	WIP	Test	Grand Total
John	2	1		1	4
Luke		2	1	3	6
Chris	1	1	1	2	5
Tom	1	1	1	2	5
Grand Total	4	5	3	8	20

<tbody>
</tbody>

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 ).

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[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!

Count of Task	Column Labels
Row Labels	Done	PPC	Grand Total
John	66.67%	33.33%	100.00%
Luke	0.00%	100.00%	100.00%
Chris	33.33%	66.67%	100.00%
Tom	33.33%	66.67%	100.00%
Grand Total	33.33%	66.67%	100.00%

<tbody>
</tbody>

 

[RoryA]

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