can't create a calculated item in a pivot table

[alexmessinger]

I have a table with columns for 1 instructor name, 2 course title, and 3 "was the instructor knowledgeable in their subject area". Values for col 3 are "agree/disagree". I have created a pivot with instructor name as the row label, "was the inst knowledgeable.." as the col label, and "was the inst knowledgeable.." as the value. This tallies the responses for each instructor across courses. I want to find the ratio between Agree and Total responses. However, I get the following message when trying to insert a calculated item. "If one or more fields in the PivotTable have calculated items, no fields can be used in the data area two or more times, or in the data area and another area at the same time. If you are trying to add a field, remove the calculated items and add the field again. If you are trying to add a calculated item, change the PivotTable report so that no field is used more than once and then add the calculated item." However, I can't get the data to populate without having the "was the inst knowledgeable.." field in the Col labels and values.

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

Since you're using the Count of the Records and not numeric values, you could instead use "Count of Course Title" in the Values area provided that there are no blanks in that field.

The calculated item would be: =Agree/(Agree+Disagree)

Be aware that when using calculated items, the calculated items will be counted in totals and sub-totals.

An alternative to Calculated Items that might work for you would be to summarize by "Count of Course Title" then use the option "Show values as % of Row Total".

 

[alexmessinger]

Hi Jerry-

The 'alternative' that you mention below worked well. Thank you!

Hi and Welcome to MrExcel,

Since you're using the Count of the Records and not numeric values, you could instead use "Count of Course Title" in the Values area provided that there are no blanks in that field.

The calculated item would be: =Agree/(Agree+Disagree)

Be aware that when using calculated items, the calculated items will be counted in totals and sub-totals.

An alternative to Calculated Items that might work for you would be to summarize by "Count of Course Title" then use the option "Show values as % of Row Total".