% in Pivot Tables

[ArcticWolf]

Hi,

I have a pivot table with 2 fields in 'row'. 1st is Job Role, 2nd is
Payscale (there are many payscales for each job role). 'Data' is suming the
total people for each payscale.

I want to get a % of each payscale as a total for the 1st field and not for
the whole column or the row.

Job Role,Payscale,# of people,% of Job role
Accountant,Band1,1,6.67%
Band2,2,13.33%
Band3,4,26.67%
Band4,1,6.67%
Band5,7,46.67
Account Total,,100, 100%
Advisor,Band1,10,33.33%
Band2,5,66.67%
Advisor Total,,15,100%

TIA,

AW

 

[Ashish Mathur]

Hi,

There is no such inbuilt functionality in Excel 2007 and prior versions -
the default if % of column total. One workaround could be the following:

1. In a spare column (give it a heading % of job role), enter
=sumproduct(($A$2:$A$500=$A2)*($B$2:$B$500=$B2))/countif($A$2:$A$500,$A2).
Format this as % age
2. You may now drag % of job role to the data area

I have assumed that A2:A500 has job roles and B2:B500 has payscales

Hope this helps.

Just to let you know, in Excel 2010, one can see the % of subtotal.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com