+ Reply to Thread
Results 1 to 5 of 5

Pivot Tables and Calculated Fields

  1. #1
    Registered User
    Join Date
    11-19-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    9

    Pivot Tables and Calculated Fields

    I have a list of transactions of a specifc type (raw data sheet). Each row of data is assigned to a specific Area, Location, and Work Group.

    I want to find out what percentage of the total volume of transactions this chunk of transactional data represents. I'd like to display these results in a pivot table so I can show a percentage breakdown for Area > Location > then Workgroup.

    My problem is finding out the function of the pivot table that allows me to divide a COUNT (of the transactional data) by a constant (total volume of transactions). When I do a calculated field, I keep ending up with something like 26,000% when the real answer should be < 1%.

    Any suggestions on how to accomplish this? When I enter the formula for the calculated field I'm putting "=COUNT('CustomerID')/'Transaction Volume'". Does this seem wrong? Anything helps.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pivot Tables and Calculated Fields

    Hi,

    You're probably over complicating this by using a calculated field.

    In the Value Field Settings for the PT, on the 'Show Values As' tab, pick the '% of Total' option.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    11-19-2012
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Pivot Tables and Calculated Fields

    Hi Richard,

    Thanks for the reply. I was wondering what you meant about the Value Field settings? I have to apply these settings to a specific column in the PT right? How do I make one of these columns say "335/380867" for example?
    Attached Files Attached Files

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

    Re: Pivot Tables and Calculated Fields

    you can't do that in a 2007 pivot table unless you can add a calculation to the source data-impossible to say from a word document with a screenshot
    Josie

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

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pivot Tables and Calculated Fields

    Hi,

    As a point of clarification, when I said you're over complicating things by using a calculated field, I meant a calculated field in the PT functionality not a calculated field per se.

    If you are wanting to show a string of alpha characters as you describe then Joseph is correct. On the assumption that you already have two fields in your data which have the two numbers 335 & 380698 then in a new column add

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    then pick this field for your PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculated Fields in Pivot Tables
    By RobertL in forum Excel General
    Replies: 1
    Last Post: 07-06-2007, 07:50 PM
  2. Calculated fields in pivot tables
    By Nigel Drinkwater in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-03-2006, 01:45 PM
  3. Calculated fields in Pivot Tables
    By lj in forum Excel General
    Replies: 2
    Last Post: 11-15-2005, 06:20 AM
  4. [SOLVED] Pivot tables - calculated fields
    By Arls in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-12-2005, 09:05 AM
  5. [SOLVED] pivot tables - calculated fields
    By Esche in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2005, 05:15 PM

Tags for this Thread

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