Pivot Table based on specific values from multiple columns

**Jaypoc** · 09-20-2012, 04:29 PM

I have been trying to build a pivot table based on two separate criteria that enables you to drill-down accurately.

For instance:

Each row in my raw data contains a region (N, S, E or W) and a series of "flags". Human (1 or 0), Dog (1 or 0), Male (1 or 0), Female (1 or 0) and Confirmed (1 or 0):

REGION	HUMAN	DOG	MALE	FEMALE	CONFIRMED
North	1	0	1	0	1
South	1	0	0	1	1
South	0	1	0	1	1
East	1	0	1	0	0

... and so on in this format

I want to create a pivot that groups the various "flags" by region as follows:

	North	South	East	West	Total
Humans	54	4	12	21	91
Confirmed	23	19	2	8	42

I have this which works, but when you click on a value to see the details, it filters by the Region, but not by the "flag" column that row is based

Column Labels:

REGION

Row Labels:

VALUES

Values:

Sum of Humans
Sum of Confirmed

This displays properly, but clicking on any of the values returns all the values for a given column, but shows all 1's and 0's rather than just the 1's which is what I need. Is there a way to perform some type of conditional Count Of rather than using Sum to get the values?

Such as:

Count Of Humans Where Value equals 1
Count of Confirmed where value equals 1

Is there a way (preferable an easy way that I'm overlooking) to do this?

Thanks in advance!

**simarui** · 09-20-2012, 05:03 PM

Move Confirmed to Report Filter and select 1

**Jaypoc** · 09-21-2012, 09:17 AM

Doing that would filter the "Humans" row as well. I need to perform that exact filter, but ONLY on the "Confirmed" row.

The data I am displaying is accurate, but the problem is when I double-click on a number, it shows all the records because I am using the "Sum Of" value which just adds all the 1s and 0s. Ideally, the columns would have "Yes"/"No" values and I would want to do a Count Of "Yes" values so drilling down would present a view filtered by the selected Region AND only the "Yes" rows

**gptp20** · 06-21-2013, 07:23 PM

Hi,

Did you ever find a resolution to this issue? I have the exact same problem. Built a great report but users need to drill down. Only after building the report did I find out that I can't drill down to the Sum value. For example, for confirmed/north I would want 23 but I get 42.

Thanks!

**VKS** · 06-22-2013, 12:33 PM

Hello Gptp,
Welcome to the fourm.
Suggest you start a your own thread for two reasons
1. This is an old thread
2. Please take some time out and read the forum rules as your post may be termed as hijacking someone elses thread
If possible add the sample file by clicking on go advanced at the bottom left and then add a sample data file by clicking on paper clip icon.
Best Regards/VKS