Running Excel 2010. No Power Pivot or Power Query Add ins. Data source is a Sharepoint aggregation of monthly InfoPath form submissions.
The underlying data file contains monthly reports from 11 locations capturing 50 data items. Pivot table displays time on the horizontal axis, data elements on the vertical.


This example focuses on five data items reported every month. Pivot filters are set up on the table to show calendar years, fiscal quarters, location codes (11 sites) and regional managers who oversee the sites.
Employees at each location sign up to attend training. A sign up count is shown, along with counts of those who actually show up and those who don't, by reason.
Pivot table displays counts for each of these data elements, by month. I want the table to also dynamically perform the calculations for % Attended, % Cancelled, % Missed-Unexcused and % Missed-Supv Error. The function denominator is the count of those who signed up.
The example is using manual formulas (Attended/Signed Up; Cancelled/Signed Up, etc.) I want Excel to calculate these on the fly, and I want these calculations to be sensitive to the filtering options at the top. So, If I select "Year" 2015 and 2016, the table will display 19 monthly data columns, a running total, and 19 columnar entries showing the four attendance rates. Same with choosing one or more Fiscal Quarters, one or both regional managers, and one or more of the 11 sites.

Attached Files

Calculated columns within Pivot Table.xlsx (11.0 KB, 9 views)

If you can be a bit flexible with the position of the percentages, try adding the field being counted (number of students) into the Values area of the pivot table. Field settings for the second instance of the field will be "sum" and Show Values As will be "% or Grand Total".
Good luck with it.

Thanks for your response. For some reason, the subtotals do not calculate properly. I am trying to get percentages of a particular column value. That means the numerator and denominator are in the same column of data. The "Show values as" functionality calculates the percentages for each row total even when I select "% Of" and specify the field. The dialog box wants me to specify a base, which I don't understand.

So, for example, here is what columns look like:
Jan Feb
Signed up 10 12
Attended 8 9
Cancelled 1 1
Missed 1 2

What I want to see are the following ratios:

% attended 80% 75%
% cancelled 10% 8%
% missed 10% 17%

For some reason, the formula returns the percentage of the row:

% attended 47% 53%
% cancelled 50% 50%
% missed 33% 67%