Thread: Calculating percent of one field over the sum of another

I am trying to use multiple response data in a pivot table.

A survey was given to students asking them to check the colors they like. They could select as many as they wanted among Green, Blue, Red.

For example:

- 7 out of 7 (100%) females in School ABC selected Green.
- 5 out of 7 (71.4%) females in School ABC selected Blue.
- 2 out of 7 (28.6%) females in School ABC selected Red.
- similar data for the males in the school, and for students from other schools.

The data I have is aggregated like so:

School Gender StudentCount Color ColorCount
ABC    Female  7           Green         7
ABC    Female  7           Blue           5
ABC    Female  7           Red            2
ABC    Male    8           Green           8
ABC    Male    8           Blue            5
ABC    Male    8           Red             3
etc.

In the pivot table I can't figure out how to calculate the percents (7/7 = 100%, 5/7 = 71.4%), etc.

My Pivot table has StudentCount and ColorCount in the data area. My hope was to change the options for ColorCount to show data as the "% of" and select the StudentCount variable, but that's not an option.

Any suggestions? The data with pivot table is attached.
.

Originally Posted by Azam Ali

see attatchment

is it help?

Ah! So simple. Thank you!

For others: the solution is to create a calculated field (called %), with this formula: =ColorCount/StudentCount.

Put ColorCount (ask for Sum), StudentCount (as for Sum), and % in the data area.

A downside to this multiple response pivot table is that it creates rows that represent duplicate cases. So if you want to analyze data other than the multiple response fields, and for instance show the total number of students by gender, it won't be accurate. With this data it'll show 21 females (7 + 7 + 7) and 24 males (8 + 8 + 8). The only work around I've come up with is to add a new field, "Unique," with a 1 for the first unique row and a 0 for the rest. As in:

School Unique Gender StudentCount Color ColorCount
ABC 1 Female 7 Green 7
ABC 0 Female 7 Blue 5
ABC 0 Female 7 Red 2
ABC 1 Male 8 Green 8
ABC 0 Male 8 Blue 5
ABC 0 Male 8 Red 3
etc.

For any analyses that doesn't involve the colors, filter on Unique to include only rows with a "1."