Calculating filtered data against itself in a Pivot table

[alon91]

My first question on MrExcel.com, although I've come to this site dozens of times via Google.

Sorry for the strange subject line -- I have no idea how to describe this problem in one sentence (it's hard to explain even in several paragraphs).


THE SETUP:

I have raw data from a *satisfaction survey* in the following format:

--------------------------------------------------------
|QUARTER	GENDER	OCCUPATION	SATISFACTION
--------------------------------------------------------
|2006Q1		MALE	STUDENT		5
|2006Q1		MALE	RETIRED		4
|2006Q1		FEMALE	COMPUTERS	2
|2006Q2		MALE	STUDENT		1
|2006Q2		FEMALE	STUDENT		5
|2006Q2		FEMALE	COMPUTERS	5
--------------------------------------------------------

and place it into a pivot table that looks like:

-------------------------
|GENDER	2006Q1	2006Q2
-------------------------
|MALE	2	1
|FEMALE	1	2
-------------------------

I then made 'SATISFACTION' a Page Field, because I'm interested in the percentage of people who selected 4 or 5 in satisfaction. I filtered 1s, 2s, and 3s by double clicking on the Page Field and highlighting the items I wanted to hide in the "Hide Items" section of the "PivotTable Field" dialog box. The resulting table looks like:

-------------------------
|GENDER	2006Q1	2006Q2
-------------------------
|MALE	2	0
|FEMALE	0	2
-------------------------

THE PROBLEM:

The second Pivot Table gives me totals -- I need to calculate the PERCENTAGE of satisfied people (people who picked 4s and 5s in satisfaction) for each category. Essentially I'm looking to divide the data in the filtered Pivot Table by the data in the unfiltered Pivot Table.

I've attempted several different methods with no luck. Calculated Fields look somewhat promising, but haven't been able to figure out if they can get the job done. While I'd Ideally like to make these calculations appear in a PivotTable so that it look good for presentation and allows for each charting in PivotCharts, I'm really open to all solutions.


LESS IMPORTANT BACKGROUND INFO:

For completeness, I'll mention that there are actually dozens of questions in this survey (and columns of raw data). I currently have a List Box containing questions -- when the end user selects a new question, I have VBA code to swap out the Row Field of the Pivot Table and replace it with a Row Field of the new question. The code for this is:

Sheets("sheet2").PivotTables("pt_display").RowFields(1).Orientation = xlHidden
Sheets("sheet2").PivotTables("pt_display").PivotFields(Range("questions").Cells(Sheets("Sheet2").Shapes("row_attribute").ControlFormat.Value).Value).Orientation = xlRowField


This works great, and I'd ideally like a similarly elegant solution for my problem above. I'm hoping for a solution contained in a Pivot Table but would also be grateful if anyone had tips on writing a subroutine that will neatly divide values in two pivot tables with identical layouts (the filtered vs. the unfiltered) and place the data in a way that can be charted.


I'd be unbelievably thankful for any help with this.

Alon

 

[Fazza]

Hi Alon,

Instead of hiding the unwanted "satisfaction" field values with pivot table functionality, hide them only by the normal worksheet hiding of rows.

Group the "satisfaction" results in the pivot table: values 1, 2 & 3 in one group and values 4 & 5 in the other. Get the pivot table how you want it and then simply hide the worksheet rows that you don't want to see. The percentage calculations will work normally on the full data but will display as you want. OK?

regards,
Fazza