I have a pivot table that summarizes instructor rank by gender for each
Faculty and department.
Prof Assoc Prof Assist Prof
Instructor Lecturer Total Faculty Dept M F M F
M F M F M F

Arts Arch
Arts Psyc
Sci Math
Sci Phys

The pivot table will show a grand total for each row (ie for each
department). However, it is a single number and not broken down by gender.
When I click on 'rank' and try to add a calculated item called 'Grand Total'
with the formula: Professor + 'Associate Professor' + 'Assistant Professor' +
Instructor + Lecturer, I do get a new column called 'Grand Total' with a male
and female breakdown. But now each Faculty shows all possible departments
instead of just the departments that belong in that Faculty. For example,
Math shows up in all Faculties but should just show up under Faculty of
Science. The data are blank for all Math rows except for the one in Science.
How do I get rid of all the blank Math rows except for the one in the
Faculty of Science?
Thanks,
Liny.