Showing values in a pivot table as a percentage of value in another pivot table

dropjinx

New Member
Joined
Jul 20, 2022
Messages
1
Office Version
  1. 2021
Platform
  1. Windows
Apologies if the title is not the best wording, I am struggling to explain my issue succinctly, but greatly appreciate any help.

I have a pivot table that draws on survey data. The survey question was multiple choice, and allowed for up to 3 options to be selected.

My end goal is to have the pivot in screenshot 1, show percentages rather than a count. But these percentages need to be of the specific segmented demographic i.e. how many Asian (variable 1: geography) 18- 24 year olds (variable 2: age bracket) selected the response 'reading'. Currently, the table illustrates that the response was selected 3 times by 7participants who fall into this group, however, what I would like to show is the percentage i.e. 7 out of a total 16 respondents therefore 43%.

I have explored using 'show value as' and the various options with parent total row, however, this does not take into account that individuals could respond with up to three choices, some responding with less. This throws all the percentages out e.g. for this group there were 45 total responses selected across the 16 respondents, but there could have been up to 48 responses selected by the same group.

Screenshot 2 shows the a sample of the demographic make up of the respondents, within another pivot table.

Can anyone help show me what I'm missing? I am fairly fluent in excel, and on a learning journey with pivots specifically, and have no experience in VBA so would like to avoid if possible.

Note: Both screenshots show segments of a much larger database, and the solution would be applied to multiple responses that follow the same categorical criteria.

Thanks in advance!
 

Attachments

  • eg2.PNG
    eg2.PNG
    3.3 KB · Views: 3
  • eg1.PNG
    eg1.PNG
    7.2 KB · Views: 3

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,175,887
Messages
5,900,073
Members
434,817
Latest member
nbretscher13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top