Pivot issue

[SD]

Greetings everyone,

I am just not able to get the pivot to give me % of column subtotals

When I do it with one variable I get the right numbers like this :

2007 2008 2009
A John 80% 75% 60%
Pat 20% 25% 40%

Now when I add another variable B I get it as

2007 2008 2009
A John 20% 20% 15%
Pat 20% 30% 20%
B Emily 30% 30% 30%
Jane 30% 20% 35%


I would like to have A and B to have their own percentages and not the
percentage of the entire column. Would really appreciate if any one
could point out where I am missing out or bluntly help me get over
this

basically like this

2007 2008 2009
A John 80% 75% 60%
Pat 20% 25% 40%
B Emily 45% 60% 25%
Jane 55% 40% 75%

Which shows a percentage of A and B separately.

Thanks in advance.

regards
SD

 

[JoelS]

Greetings everyone,

I am just not able to get the pivot to give me % of column subtotals

When I do it with one variable I get the right numbers like this :

                2007    2008    2009
A       John    80%     75%     60%
        Pat     20%     25%     40%

Now when I add another variable B I get it as

                2007    2008    2009
A       John    20%     20%     15%
        Pat     20%     30%     20%
B       Emily   30%     30%     30%
        Jane    30%     20%     35%

I would like to have A and B to have their own percentages and not the
percentage of the entire column.  Would really appreciate if any one
could point out where I am missing out or bluntly help me get over
this

basically like this

                2007    2008    2009
A       John    80%     75%     60%
        Pat     20%     25%     40%
B       Emily   45%     60%     25%
        Jane    55%     40%     75%

Which shows a percentage of A and B separately.

Thanks in advance.

regards
SD

One possible solution is:

Multiply your numbers by .01
format your pivot table field for numbers as percentage with 0
decimals
show data as normal
format your pivot table field for variables with automatic subtotals

 

[Shane Devenshire]

Hi,

We don't have access to the subtotal calculations in the pivot table, so the
only way to handle this is to use formulas in the spreadsheet.

 

[Herbert Seidenberg]

Excel 2007
Two ways:
Source data helper columns,
Auxiliary Table with macro.
Neither easy.
http://www.mediafire.com/file/mt3dmmjzlto/03_17_09a.xlsm

 

[SD]

Thanks every one .. however ... could not work out the solutions
mentioned ... by the way I am working on Excel 2003.

Any one else can help, would really appreciate it .

many thanks
SD