+ Reply to Thread
Results 1 to 4 of 4

Help Needed: Calculation Within A Pivot

  1. #1
    Registered User
    Join Date
    12-11-2013
    Location
    Iowa
    MS-Off Ver
    Excel 2010
    Posts
    2

    Help Needed: Calculation Within A Pivot

    Hello,

    I'll try to explain this the best way possible, and add some pictures to explain the situation. What I need is to add a field that gives a gross margin percentage for a project.

    To begin, I simplified the following data that the Pivot Table is using. Project (10 different projects), Account Number, Account Category (Revenue, and Cost), Debit Amount, Credit Amount, and a manually inserted column named Total (CR Amount - DR Amount). See 1st image. Pivot1.jpg

    I have designed the Pivot table so it lays out like this. Pivot2.png Pivot3 (2).png

    I have been trying to enter a calculated field that would automatically provide the Gross Margin Percentage. In the 2nd image, the Grand Total is the gross margin, but I am unable to use that as a selection when creating a calculated field.

    If someone could provide some assistance, it would be much appreciated.

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Help Needed: Calculation Within A Pivot

    Hi Smtesar,

    It sounds like you are familiar with the 'Calculated Field' feature.

    Once you have named the new field & added the Name & Formula - which in this case would be;
    Name - Gross Margin Percentage
    Formula - CR Amount - DR Amount

    Then click on 'Add' & the field will appear in the list of fields.
    Click 'OK'

    After that, the field should appear in the available Pivot fields, at what point is this failing?
    Remember you are unique, like everyone else

  3. #3
    Registered User
    Join Date
    12-11-2013
    Location
    Iowa
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Help Needed: Calculation Within A Pivot

    Hello noboffinme,

    I really appreciate your response, and I apologize for replying so late. I created a Calculated Field called "Gross Margin" with the formula you have above, then I created another called "GM %" with a formula of "Gross Margin" / "Sum of Revenue". The formula works perfectly, but the design is irritating to me. Here is a screen shot of what it looks like.Pivot 12.30.13.png

    I would prefer it to have a condensed design, the GM% is right next to the total column. Project Description, Revenue, Cost, Grand Total (Gross Margin), and then Gross Margin Percentage. Pivot Example.png This is not a pivot table, but another worksheet that i copy data too.

    I appreciate your help.

  4. #4
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Help Needed: Calculation Within A Pivot

    OK, try this,

    Right click inside the Pivot & select 'Pivot Table Options' >> 'Totals & Filters'.

    You can uncheck the checkboxes that show the Grand Totals for Columns &/or Rows.

    Let me know if this isn't what you wanted.

    Cheers

+ 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. Time calculation help needed
    By frustrated in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-05-2013, 07:16 PM
  2. Replies: 1
    Last Post: 01-15-2013, 08:51 AM
  3. Probability Calculation Help Needed
    By Kiffar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-13-2012, 10:52 AM
  4. Formula Calculation problem Help needed please
    By littlelax in forum Excel General
    Replies: 2
    Last Post: 01-18-2012, 02:09 PM
  5. If & calculation formulas needed
    By Frazzfreeman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-18-2008, 08:07 AM

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