+ Reply to Thread
Results 1 to 4 of 4

Pivot Table Sub-Total and Total Settings for Percentages

  1. #1
    Registered User
    Join Date
    10-17-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2016
    Posts
    99

    Pivot Table Sub-Total and Total Settings for Percentages

    Hello,

    On the attached worksheet I have everything set up how I would like in a pivot table, however I am wondering where or what the setting should be for subtotals of the Profit Margin category?

    The sheet currently auto-sum's the column, which i'm sure is default. How do I change it so that Cells I19, I34, and I36 are not added up, but are actually the quotient of the 2 cells before?

    This is exactly what i'm looking to accomplish:

    I9=(H9/G9)
    I34=(H34/G34)
    I36=(H36/G36)

    Thanks,

    Adam
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-09-2016
    Location
    Perth Western Australia
    MS-Off Ver
    Office 365
    Posts
    257

    Re: Pivot Table Sub-Total and Total Settings for Percentages

    I assume you want subtotals for each month. If that is the case - Select a cell in the month column of your pivot table and right click.. It will giv eyou the option to subtotal

    Does this help

  3. #3
    Registered User
    Join Date
    10-17-2013
    Location
    San Diego
    MS-Off Ver
    Excel 2016
    Posts
    99

    Re: Pivot Table Sub-Total and Total Settings for Percentages

    No. What I'm looking for is the Margin percentage for INTER Total, DIRECT Total, and GRAND Total (Cells I19, I34, and I36). Currently it only gives the SUM of the column.

    This would be achieved by dividing the previous 2 columns, so:

    I19 would equal H19/G19
    I34 would equal H34/G34
    I36 would equal H36/G36

    Is there a setting for this?

    Adam

  4. #4
    Forum Contributor
    Join Date
    01-09-2016
    Location
    Perth Western Australia
    MS-Off Ver
    Office 365
    Posts
    257

    Re: Pivot Table Sub-Total and Total Settings for Percentages

    My misunderstanding

    Click in the pivot table to get the pivot menu
    Insert / Calculated field

    Enter formula Margin/Invoice
    Set Format to %
    Remove existing margin from pivot table

    This should work

    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. Replies: 6
    Last Post: 09-29-2015, 08:44 PM
  2. Replies: 1
    Last Post: 11-27-2013, 07:59 AM
  3. Pivot Table Total doesn't match data total
    By CNL.Excel in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-31-2013, 07:40 PM
  4. Running total on percentages in Pivot table
    By Wayne Dunlop in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-13-2012, 03:46 AM
  5. Pivot Table - Sub Total & Grand Total Filter
    By ramki in forum Excel General
    Replies: 1
    Last Post: 08-07-2009, 11:40 PM
  6. [SOLVED] % of Running Total to Grand Total in Pivot Table
    By David in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2005, 04:05 PM
  7. [SOLVED] Pivot Table percentages-give the total number of accepts
    By cdb in forum Excel General
    Replies: 2
    Last Post: 03-22-2005, 06:06 PM

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