Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello
    Does anyone know of a way I can create %s of subtotals in a pivot table - instead of % of grand total. I need to create a table from a cube which has salary by region over time with the number of people in each salary range as a percentage of the region. I have tried using the % Of setting but it comes up with nonsense values of itself, ie. each salary range is 100%.
    Cheers
    Suzie

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by suziep View Post
    Hello
    Does anyone know of a way I can create %s of subtotals in a pivot table - instead of % of grand total. I need to create a table from a cube which has salary by region over time with the number of people in each salary range as a percentage of the region. I have tried using the % Of setting but it comes up with nonsense values of itself, ie. each salary range is 100%.
    Cheers
    Suzie
    Afternoon Suzie

    Pop over to the CONTEXTURES site, Debrah Dalglish has some super examples. Please drop her a mail if it helps you out
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by B. Bunter Esq. View Post
    Afternoon Suzie

    Pop over to the CONTEXTURES site, Debrah Dalglish has some super examples. Please drop her a mail if it helps you out

    Thank you B. I did not manage to find a way to do a percentage of the subtotal and given the costs of consultation I was loath to email.
    It seems like none of the regular texts or help sites go beyond what is standard or seem able to provide what I am looking for. Subtotals are such a normal feature within pivots that I can't believe you can't create a % of a subtotal.
    My pivot table will also be refreshed with the database so creating a separate column with a formula seems like a waste of time. I am hoping there is someway to create percentages of subtotals within the pivot so that it updaes with the pivot is refreshed.
    Cheers
    Suzie

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by suziep View Post
    Hello
    Does anyone know of a way I can create %s of subtotals in a pivot table - instead of % of grand total. I need to create a table from a cube which has salary by region over time with the number of people in each salary range as a percentage of the region. I have tried using the % Of setting but it comes up with nonsense values of itself, ie. each salary range is 100%.
    Cheers
    Suzie
    What version of Excel?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Catharine Richardson - WebGenii View Post
    What version of Excel?
    Excel 2007

    can use 2003 if necessary

    suzie

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can't do it within the table (I can't recall if it has been added to 2010). You have to add calculations to the underlying data if you need to do that. (and yes, I agree it's daft)

    Edit: just tested in 2010 and there is indeed a '% of parent row/column' option finally!
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Jun 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    You can't do it within the table (I can't recall if it has been added to 2010). You have to add calculations to the underlying data if you need to do that. (and yes, I agree it's daft)

    Edit: just tested in 2010 and there is indeed a '% of parent row/column' option finally!

    Excellent! Thanks Rory.

Posting Permissions

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