From: ArcticWolf on
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

From: Ashish Mathur on
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

"ArcticWolf" <ArcticWolf(a)discussions.microsoft.com> wrote in message
news:C1D08CD3-991D-498A-9AB7-499CD5842CCD(a)microsoft.com...
> 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
>