Pivot Table - Example Given

[TASHFIN]

Thanks for the advice Jay, it certainly put me on the right track - Unfortunatley it did mean having 32000 records but I did end up in the end with % on each line!


Cheers !

 

[TASHFIN]

Hi Guys,

I need some help with a Pivot Table. I have two fields in the "ROW" section of the Pivot Table , nothing in the "COLOUMN" section but
five numerical columns representing totals for five years in the "DATA" section. Now the two fields in the Row section - the first field on the left is an individual unique record while the second field from the left splits each of the unique record by its natural variants. The totals in the Data section for the three years are split at the level of this second field. However by having the Automatic sub-totals on the first field on the left, we have the total for each unique record irrespective of how many variants it has. So I have the individual variants totals and then the unique record total below. What I want while remaing in Pivot table mode is to add three columns besides the existing data, which gives the % of each variant "within" the unique record. So for example if you have three variants for a unique record (refer to example at bottom). So what I want to have in the new column in the pivot is the % of the varaint for each unique record. So in the new column for the example above , the value would be 33.33% while the record would be 66.66%%.


Here is one unique record after doing a Paste Special Values on the Pivot Table for one unique record.

Data
Field 1 Field 2 1996 1997 1998

11161084 ESTATE 16168 13064 4169
HATCH 8291 4532 1585
SALOON 2230 1893 1263
11161084 Total 26689 19489 7017
Grand Total 26689 19489 7017


So what I want in the pivot is in the 4th column besides the existing data

60.58%
31.07%
8.36%

If you have any ideas let me know

 

[Jay Petrulis]

On 2002-04-13 22:36, TASHFIN wrote:
Hi Guys,

I need some help with a Pivot Table. I have two fields in the "ROW" section of the Pivot Table , nothing in the "COLOUMN" section but
five numerical columns representing totals for five years in the "DATA" section. Now the two fields in the Row section - the first field on the left is an individual unique record while the second field from the left splits each of the unique record by its natural variants. The totals in the Data section for the three years are split at the level of this second field. However by having the Automatic sub-totals on the first field on the left, we have the total for each unique record irrespective of how many variants it has. So I have the individual variants totals and then the unique record total below. What I want while remaing in Pivot table mode is to add three columns besides the existing data, which gives the % of each variant "within" the unique record. So for example if you have three variants for a unique record (refer to example at bottom). So what I want to have in the new column in the pivot is the % of the varaint for each unique record. So in the new column for the example above , the value would be 33.33% while the record would be 66.66%%.


Here is one unique record after doing a Paste Special Values on the Pivot Table for one unique record.

Data
Field 1 Field 2 1996 1997 1998

11161084 ESTATE 16168 13064 4169
HATCH 8291 4532 1585
SALOON 2230 1893 1263
11161084 Total 26689 19489 7017
Grand Total 26689 19489 7017


So what I want in the pivot is in the 4th column besides the existing data

60.58%
31.07%
8.36%

If you have any ideas let me know

Hi,

I rearranged your data a bit by putting two row fields

1. Item Number
2. Year

and one column field
1. Type (Estate, Hatch, Saloon)

I then put the amount in the data field, with Summarize By = SUM
and
Show Data As = % of Row

It returned the following (choose to reply with quote or edit to see it arranged correctly):

Sum of Amount Type
Number Year ESTATE HATCH SALOON
111610841996 60.58% 31.07% 8.36%
1997 67.03% 23.25% 9.71%
1998 59.41% 22.59% 18.00%
111610851996 60.58% 31.07% 8.36%
1997 67.03% 23.25% 9.71%
1998 59.41% 22.59% 18.00%

I made up the second item with double the original amounts but same proportions.

This was a second pivot table. Possibly this would work for you.

HTH,
Jay

 

[Jay Petrulis]

Hi,

What exactly did you do? I was playing around with some ideas for you, but didn't quite actually succeed in giving you the data and the % in one table, as you requested.

It appears you did, so let us know. It would be very useful to many, including me.

Regards,
Jay