Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Pivot Table - Example Given

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    LONDON , ENGLAND
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 !

  2. #2
    New Member
    Join Date
    Mar 2002
    Location
    LONDON , ENGLAND
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •