Thread: Pivot Table Headache

Hey so i've been teaching myself excel and have managed to knock up a nice pivot table but have run into what may be a bit of n00b roadblock.
Basically the data i get would be something like the following columns
UNIQUE ID POSITION TITLE DEPARTMENT LOOKUP CODE GENDER(M/F)


Using these columns I turn the lookup code into POSITION GROUP and create an MALE (1 if gender is M else 0) and FEMALE(1 if gender is F else 0)

So the pivot table columns would be:
DEPARTMENT- overall filter
POSITION GROUP- First row label
POSITION TITLE - Second row label
Count of UNIQUE ID- Value
Sum of MALE- Value
Sum of FEMALE- Value

This seems to work ok and displays the total number of men then the total number of women for each job title within each position group
But my problem is once i have the totals for male and female I need to have a percentage field next to each for percentage male vs female

This is what i really can't get my head around I would think i could create a duplicate column for each and then a percentage of function when displaying it but what do i choose it to be a percentage of in that case???

Any advice would be greatly appreciated!
Feel free to flame if this is a retarded question :P

Its [Department] [LookupCode] Sorry it looked better before the spaces wee formatted out :S
Will upload an example as soon as i have access to excel again, currently working on my tablet

Example.xlsx
OK so attached should be some dummy data
the way i see it if i'm gonna add a percentage it'll have to be in the pivot table itself as otherwise i can't see how you could filter by department

Originally Posted by OnErrorGoto0

It is easier to add the Gender field as column headers and skip your added fields. See the attached, assuming that is what you wanted.

The problem with this is that there are quite a few fields that i need to do this to and I can't have them being broken down too much. Unless there is a way to add 2 Column labels that reference the same row labels but that dont effect each other???

Calculated fields are one of the main things I don't understand about pivot tables. Can anyone suggest a good tutorial? the ones i have found have been quite unhelpful.

Thank you all again for your patience

Originally Posted by OnErrorGoto0

I am probably being dim but I do not really follow this. Have you tried Kyle's suggestion?

If I recall correctly, calculated fields are on the Formulas button in the Pivot Table Tools tab. They are not that complicated - you just have to remember that the fields are always aggregated before the operations are applied, so for example if you add one that is Field1/Field2, then for each total, what you get is Sum(Field1)/Sum(Field2) and not Sum(Field1/Field2)

I am currently trying to work out Kyle's solution, I was replying to your earlier post.
Ive done a calculated field that references the male and female columns and it seems to work but i have to test it with more data. It could just be me being paranoid but I don't understand why it works :S
already much better than I had before though thanks guys!