+ Reply to Thread
Results 1 to 9 of 9

Calculated fields in a Pivot table

  1. 05-21-2012, 02:11 PM #1
    dwhite30518
    dwhite30518 is offline
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Calculated fields in a Pivot table

    I am trying to determine if figuring out a calculated field in a pivot table would be easier(or even possible) rather than creating a table or list to do the calculations. I have created a pivot table and would like to create a column that shows the difference between the 2 columns. Without having to use PowerPivot, how can I accomplish this? I have attached the worksheet I am working with and below is my current Macro for creating the pivot table.

    Please Login or Register  to view this content.

    Thanks!!!!
    Reply With QuoteRegister To Reply
  2. 05-21-2012, 02:56 PM #2
    Pichingualas
    Pichingualas is offline
    Forum Contributor
    Join Date
    02-07-2012
    Location
    MIA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    429

    Re: Calculated fields in a Pivot table

    Why not just add a column with a formula and drag it down?

    Look at the attached file to see what I mean.

    New_Main_RSA_Tab 2012.xlsm

    I hope that helps.
    .?*??)
    `?.???.?*??)?.?*?)
    (?.?? (?.?    Pichingualas <---
    ??????????????????????????

    Wrap your code with CODE TAGS.
    Thank those who helped you, Don't forget to add to their REPUTATION!!! (click on the star below their post).
    Please mark your threads as [SOLVED] when they are (Thread Tools->Mark thread as Solved).
    Reply With QuoteRegister To Reply
  3. 05-21-2012, 02:59 PM #3
    Pichingualas
    Pichingualas is offline
    Forum Contributor
    Join Date
    02-07-2012
    Location
    MIA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    429

    Re: Calculated fields in a Pivot table

    You can even apply the same format as the rest of the Pivot Table if thatīs important to you:

    New_Main_RSA_Tab 2012.xlsm
    Reply With QuoteRegister To Reply
  4. 05-21-2012, 03:01 PM #4
    dwhite30518
    dwhite30518 is offline
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Re: Calculated fields in a Pivot table

    2 things...First, can I create this as part of the pivot table rather than a separate column? Second, how code this for a macro?
    Reply With QuoteRegister To Reply
  5. 05-21-2012, 03:11 PM #5
    Pichingualas
    Pichingualas is offline
    Forum Contributor
    Join Date
    02-07-2012
    Location
    MIA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    429

    Re: Calculated fields in a Pivot table

    I don't see a way to add it to make it be part of the Pivot Table. This is because Pivot Tables are dsigned to summarize data from other tables, and the data you want needs to be calculated after the Pivot Table is made, so this leads to the chicken and the egg thing lol. However, making it part of your CreatePivotTable script is not really that hard. I can show you how, give me a few minutes.
    Reply With QuoteRegister To Reply
  6. 05-21-2012, 04:03 PM #6
    Pichingualas
    Pichingualas is offline
    Forum Contributor
    Join Date
    02-07-2012
    Location
    MIA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    429

    Re: Calculated fields in a Pivot table

    Sorry for the delay... accidentally deleted it and had to start over... oops lol

    I have added a new macro below your previous one which if you execute will add the new column. You can also execute your previous macro and the new one will run too, right after the pivot table is finished.

    New_Main_RSA_Tab 2012.xlsm

    I hope this helps.
    Reply With QuoteRegister To Reply
  7. 05-23-2012, 02:16 PM #7
    dwhite30518
    dwhite30518 is offline
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Re: Calculated fields in a Pivot table

    This is great...I guess I only have one more stupid question. How do I put a header on this new column?
    Reply With QuoteRegister To Reply
  8. 05-23-2012, 03:05 PM #8
    Pichingualas
    Pichingualas is offline
    Forum Contributor
    Join Date
    02-07-2012
    Location
    MIA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    429

    Re: Calculated fields in a Pivot table

    Add the lines in color:

    Please Login or Register  to view this content.
    The one in blue is more of a "just in case" thing, deleting the column in case the amount of lines in the pivot table lowers (to avoid ending up with lines with format but nohting in them). The one in red is the one you are looking for. I hope that helps.
    Reply With QuoteRegister To Reply
  9. 05-23-2012, 03:33 PM #9
    dwhite30518
    dwhite30518 is offline
    Forum Contributor
    Join Date
    05-13-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2010
    Posts
    378

    Re: Calculated fields in a Pivot table

    This did the trick with the header name. Thanks!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1