+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Pivot Table problem

  1. #1
    Registered User
    Join Date
    06-04-2012
    Location
    amesxp
    MS-Off Ver
    Excel 2010
    Posts
    1

    Pivot Table problem

    I have created a pivot table with column values for sales, cost and gross margin (GM) and gross margin percentage (GM%) sorted by customer. There are 65K rows of data. Unfortunately, the pivot table will not return the correct value by customer of the GM percentage. In the database, sales, cost and GM are values, whereas GM% is formula driven and in the pivot table the field setting for GM% is set on "AVERAGE". Is there anyway to get the pivot table to display the GM% correctly?

    Thanks for any help.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Pivot Table problem

    can you give a specific example? I reckon you probably just want to use a calculated field in the pivot table rather than the underlying formula column in the data.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,316

    Re: Pivot Table problem

    i had a look at "calc'd" fields in PT's and couldnt find any options that would give you a %. if your PT is not too long, what i have done in the past is add a calc outside the PT that is based on values INSIDE the PT, and built the formula in such a way that is the PT doesnt go down very far, the answers were hidden. i copied the formulas down way past where i thought the PT would extend to.

    Not sure if this something that will work for you, but its worth a try?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Pivot Table problem

    just create a filed that is one field divided by another and format as percentage?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1