Excel: Pivot Table - Percentage of one value from another

Question

My pivot table has 3 columns:

• Row label (expand/collapse items in each row)
• Count of Case
• Count of Assigned

My DATA sheet has 2 sets of data:

• "Case" (line item for every bug, feature, etc.)
• "Assigned" (name of the person assigned to the case)

On the pivot table, how do I display a column that effectively displays "X is Y % of Z" (assigned/case as percent)?

Example: http://i.imgur.com/ye0lkTH.jpg

In the linked image, Case (Z) is '10' and Assigned (X) is '3'. A third column should display "30%" (Y).

I've tried Calculated Field and even reduced "Assigned" to a 1 or 0 on the DATA sheet (as a pseudo "True" or "False", and then using that data in the Pivot Table) - all to no avail.

Please help! I'd really appreciate it.

Solution

Looks like you cannot use count() in the calculated fields. I would try to manipulate the source data so that you are summing numbers and not counting 'names'. Then, create a calculated field as shown below:

Comments

[Knaledge]

First, I really appreciate that you had a look and gave it a shot! I really tried searching around and called several people - all with no luck. In the back of my mind, I suspected it was trying to calc using the underlying values and not the counts themselves. So, I tried what you've suggested and the result is that the field shows "1" the whole way down. i.imgur.com/JxDsWbj.jpg Changing the "Show Value As" generally has no impact.

[Knaledge]

Thanks again. I wanted to avoid this solution but it does seem to be the path of least resistance. I really appreciate it!