+ Reply to Thread
Results 1 to 13 of 13

Pivot: Calculated Fields

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Pivot: Calculated Fields

    I'm trying to use the "Calculated Fields" technique in a pivot table, and every way I do it I can't get it to work. I've attached a sample; I'm simply trying to get the difference between my Budget and Projections. It seems simple, but I can't get it to work. Any ideas would be greatly appreciated.
    Attached Files Attached Files
    Last edited by jomili; 10-11-2010 at 10:26 AM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Pivot: Calculated Fields

    Try this example. I did it using 2010 Excel but it saved in .xls format.

    It all comes down the the Calculated Field dialog and Adding a Field name (Diff) and then inserting the other fields to use in the formula.

    hope that helps.
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pivot: Calculated Fields

    Marvin,

    Your example is exactly what I'm trying to achieve, but I don't understand how you did it. You said "It all comes down the the Calculated Field dialog and Adding a Field name (Diff) and then inserting the other fields to use in the formula", but I don't understand how you added a field name that wasn't there to begin with ("Diff"), nor how you were able to define it to be the difference of the other two fields. Would you explain further?.

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot: Calculated Fields

    1. Select a cell in the data area of the table.
    2. Choose 'Pivot Table- Formulas - Calculated field' from the Pivot table toolbar.
    3. Choose a Name for the new field, and enter the formula you want - e.g.:
    ='Budget' - 'Projections'
    then click Add.

    Bear in mind that the formulas work on the aggregated field values, rather than working on the field values for each row and then getting aggregated.
    Remember what the dormouse said
    Feed your head

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pivot: Calculated Fields

    Romper,

    I tried it the way you said (see attachment), but no go. The problem as I see it is that my source data has "O1-Budget" and "Projected" in the same column (MOS2). So, when I try to create my "Diff" field, I write the formula
    Please Login or Register  to view this content.
    , but Excel doesn't know what I'm talking about. MarvinP seems to have figured it out, but I don't understand how.
    Attached Files Attached Files

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot: Calculated Fields

    I confess I didn't look at your attachment originally. You actually want a calculated item, not a calculated field.
    1. Select B12
    2. Pivot table - Formulas - Calculated item
    3. Enter Diff for the name and:
    = '01-Budget'- PROJECTED
    for the formula, then add.

  7. #7
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pivot: Calculated Fields

    Romper,

    That was exactly what I was looking for. Thank you for helping me to work it through. I've attached my example, showing it actually worked. Only difference is instead of B12 ("DeptID") I clicked on C11 ("MOS2").

    So, in a short paragraph or two, would you be willing to explain the difference between a calculated field and a calculated item?
    Attached Files Attached Files

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot: Calculated Fields

    Easiest to think of them in terms of the underlying data:
    a calculated item is a new entry (row) in one of your data columns based on other rows, whereas a calculated field is a whole new column based on other columns.
    Does that make sense?

  9. #9
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pivot: Calculated Fields

    Maybe to others, but I think I'm slower than the average bear.

    You say "a calculated item is a new entry (row) in one of your data columns based on other rows, whereas a calculated field is a whole new column based on other columns."

    So that sounds like, if I have columns 01-Budgdt and PROJECTED, and did a calculated item, I would be doing something like adding or subtracting row 10 from row 11 in my PROJECTED column, to create my output in Row 13 or so. However, my Diff column (calculated Item) is a whole new column, not a row, based on my 01-Budget and PROJECTED columns. Is your explanation reversed, or am I not seeing it?

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Pivot: Calculated Fields

    I just got up and didn't know the difference, but will be reading the below link.

    http://www.databison.com/index.php/c...a-pivot-table/

    Amazing!! Pivot Tables still rock, but I've got to make more boxes in my brain to separate out Field from Item. They are in the same box now. I'll have to work on this.
    Last edited by MarvinP; 10-11-2010 at 10:28 AM.

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot: Calculated Fields

    Quote Originally Posted by jomili View Post
    You say "a calculated item is a new entry (row) in one of your data columns based on other rows, whereas a calculated field is a whole new column based on other columns."

    So that sounds like, if I have columns 01-Budgdt and PROJECTED, and did a calculated item, I would be doing something like adding or subtracting row 10 from row 11 in my PROJECTED column, to create my output in Row 13 or so.
    But you don't have those two columns in your source data. You have one column - MOS2.
    However, my Diff column (calculated Item) is a whole new column, not a row, based on my 01-Budget and PROJECTED columns.
    Nope - remember I am talking in terms of your underlying data, not your pivot table layout.
    Is your explanation reversed, or am I not seeing it?
    Option 2 - see above.

    See if the attached helps.
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pivot: Calculated Fields

    Thanks Marvin. Good source. Between you and Romper, I think this post has been fully satisified. Thanks for all your help.

  13. #13
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,946

    Re: Pivot: Calculated Fields

    Romper,

    Thanks for the visual. That brought it home to me. I see now I added rows to my original MOS2 column in my source; I was too busy thinking of what was in the table. Thanks for your patience and perseverance in helping me to see.

+ 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