+ Reply to Thread
Results 1 to 7 of 7

Can a subtotal Sum only unique values in a Pivot table?

  1. #1
    Registered User
    Join Date
    11-06-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    5

    Can a subtotal Sum only unique values in a Pivot table?

    I created a Pivot table in Excel. The field setting for the data's values is set to Max.

    I would like the pivot table subtotal to Sum those Max values, but when I change the subtotal field setting to Sum it adds all of the underlying data that's part of that set. Here's what the table looks like:

    Capture.PNG

    Here's a sort of text description, in case you can't see the picture.

    Row Labels Max of Count
    A 22
    Name 1 5
    Name 4 9

    B 21
    Name 1 7
    Name 5 8

    As you can see, the subtotals (in bold) are totaling all of the underlying data for that subtotal group. Is it possible to have the pivot table only subtotal the entries shown? So the subtotal for A would be 14 and the subtotal for B would be 15.

    Is this possible? Any advice?

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Can a subtotal Sum only unique values in a Pivot table?

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Can a subtotal Sum only unique values in a Pivot table?

    If you have Power Pivot, then yes you can write a measure for that. If not, you'd have to do something complicated with formulas in the source data, or outside the pivot table. Either way, a sample workbook would be useful as Glenn said.
    Rory

  4. #4
    Registered User
    Join Date
    11-06-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    5

    Re: Can a subtotal Sum only unique values in a Pivot table?

    Thank you for your responses. I should caveat this by saying it's not my data. I'm asking because someone had this question about their own data and I wasn't sure how to help.

    I've attached a data set similar to what the person was asking. The data shows the increase in headcount expected in different factories across 5 regions.

    We want the pivot to show the Max headcount increase in the region, and then (for some reason) we want the subtotal to sum only the rows listed in the pivot table. So for the East region, we want to sum the 5 and 9 max headcount increases at the Coffee Mugs and Post-It factories, but not the whole 22 headcount increase that we get if we sum all of the underlying data for factories in the East.
    Attached Files Attached Files

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Can a subtotal Sum only unique values in a Pivot table?

    Do you have Power Pivot?

  6. #6
    Registered User
    Join Date
    11-06-2014
    Location
    US
    MS-Off Ver
    2013
    Posts
    5
    Quote Originally Posted by rorya View Post
    Do you have Power Pivot?
    Yes, I do have access to PowerPivot

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Can a subtotal Sum only unique values in a Pivot table?

    Depending on what you want the Grand Total to show, you might use:

    SUMX(VALUES(Table1[Plant Type]),CALCULATE(MAX(Table1[Headcount Increase])))

    where Table1 is your source table. That would return 40 as the Grand Total since that is the sum of the maximum values for each of the Types. If you want the sum of the totals shown for each region, it would need a little more work. (I'm assuming the 10 in your sample pivot is not what you actually want).

    If you want 64 as the Grand Total - i.e. the total of the totals by region, you could use:


    SUMX(SUMMARIZE(Table1,Table1[Plant Type],Table1[Location]),CALCULATE(MAX(Table1[Headcount Increase])))
    Last edited by rorya; 09-12-2018 at 10:00 AM.

+ 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. Excel: Pivot Table with Unique values only
    By pwtt007 in forum Excel General
    Replies: 3
    Last Post: 01-24-2017, 09:44 AM
  2. Pivot table of unique values
    By johnelder in forum Excel General
    Replies: 0
    Last Post: 11-09-2015, 04:30 PM
  3. [SOLVED] Count only unique values in pivot table
    By Leahmaria26 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-25-2013, 08:56 PM
  4. Unique Values and summing using Pivot table
    By skyping in forum Excel General
    Replies: 6
    Last Post: 08-03-2010, 12:43 PM
  5. Replies: 2
    Last Post: 01-28-2010, 10:08 AM
  6. Count unique values - Pivot Table
    By Thomas Mueller in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-02-2005, 06:20 PM
  7. Unique values in pivot table
    By AmyTaylor in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-18-2005, 03:21 AM

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