Calculate % from 2 count values in Pivot Chart

[ar0sa]

Greetings all,

I have the following pivot table;

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row Lables[/TD]
[TD]Count of Eligible[/TD]
[TD]Count of Enrolled[/TD]
[TD][/TD]
[TD]Manual Calculation (Enrolled/Eligible)*[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]66%[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]25%[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]66%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]13[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
* Outside of pivot table with =GetPivotData

My first goal is I would like to calculate the % enrolled from within the pivot table - I feel like it should be simple to make these calculations, but this probably shows my unfamiliarity with Pivot Tables, because it is not simple. I would like this data within the pivot table as I need the data to be dynamic. The end goal is to view this information in a chart;

What I really want is to have a data point that shows the percentage enrolled above each series. I achieved this chart by increasing the series overlap to 100% on a clustered column chart. I feel like there may be a better way to do this, but this is all I've been able to come up with so far and I'm stuck. I'm using Excel 2013. Thanks in advance for your assistance.

Thank you,

Anthony

 

[oldbrewer]

you can find the 13 and the 7 automatically by searching for "Grand Total" in column A then offsetting 1 and 2

likewise you can find the 6 and the 4 for a and thus calculate the 66% - you can make another chart the same size as this one, scaled 0 to 100 to match your 0 to 7 then plot a vertical bar with no fill with its data label showing and position it exactly over the first chart which will now look like your chart but with a 66% at the top of the bar

 

[Herbiec09]

To calculate %age enrolled within the pivot, create a calculated field within the pivot table.

In 2010, this would be Options--->Calculations --->Fields, Items & Sets---> Calculated Field
In 2007, this would be Options--->Tools---->Formulas--->Calculated field

 

[ar0sa]

I've thought that this should be the solution, but how do I format this? I've tried and then the calculated field appears as a Sum in the Values total and gives the #Div/0 error.

 

[ar0sa]

Thanks for your reply, could I ask you to clarify a few things?

When you say "searching for 'Grand Total'" what do you mean? Also, what are you referring to in "Offsetting 1 and 2"? Thanks for your help.

 

[ar0sa]

Ah ha! I figured it out! The fields that the values were counting were text fields, and I was trying to do math with them! I just added a column with a "1" next to each text entry and now I'm able to work with them by this solution. Thanks, Herbiec!

 

[oldbrewer]

match 5 in a range 2,3,7,5,9 will find the 5

offset from say A1 above numbers in A2 to A6

=offset($a$1,match(5,a2:a6,0),2

will find the 5 and step 2 columns to the right