Excel: Pivot Table - Percentage of one value from another
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.
Answer
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:
