+ Reply to Thread
Results 1 to 2 of 2

Pivot Table Distinct Value and Calculated Field

  1. #1
    Forum Contributor
    Join Date
    05-12-2009
    Location
    Hendersonville, TN
    MS-Off Ver
    Excel 2010
    Posts
    113

    Pivot Table Distinct Value and Calculated Field

    I could use some help on this if I have any takers. Kind of new to Pivot tables and Charts. I have a 7000 row dataset that will be put into a Pivot Table. I am having an issue extracting a Distinct count of Technologist from the data. Working in Excel 2010, this is a sample pic of my dataset.

    DS1.PNG

    One compounding issue is that we have VUMC Staff and Contract Staff. I set a named range to determine if the listing is VUMC Staff (True) or (False) being Contract staff. I am really only interested in the VUMC numbers. So, Let us say we have 50 cases in a month, each covered by an individual. The same person will do a number of them and someone else will do another portion. So lets say we have 5 Distinct count of Technologist. We can then do an average case per Tech by taking 50/5 = 10 Average cases per Tech. I would then like to have the option to take these Averages and plot them on a chart with some other data.

    Here is some of the data in a Pivot Table, Last 2 columns are kind of what I had in mind. I was hoping to get all of this to where I can chart it and filter through with slicers.

    PT1.PNG

    Thanks for your consideration and help,

    Patrick

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Pivot Table Distinct Value and Calculated Field

    I'd recommend uploading sample workbook.

    For Excel 2010 and earlier, you'd need to use helper column in source table.

    For Excel 2010 specifically, if you can add PowerPivot add-in. You can use DAX measure to perform distinct count.

    For Excel 2013 or later, when creating Pivot table, if you click on "Add this data to the Data Model". Then Distinct Count becomes available in Value Field Settings.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

+ 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. Pivot Table: Calculated Field based on Running Total Field
    By EvolvingMonkey in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-07-2016, 06:27 AM
  2. Replies: 0
    Last Post: 08-15-2016, 02:07 PM
  3. [SOLVED] A pivot table field calculated using other field values as fields?
    By chrisf78 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-07-2015, 03:08 AM
  4. Replies: 0
    Last Post: 04-27-2015, 03:32 PM
  5. [SOLVED] Referring to a Sub-Field on Calculated Field Pivot Table Column?
    By figo12 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-27-2014, 02:02 PM
  6. Replies: 0
    Last Post: 06-26-2012, 09:06 PM
  7. Replies: 3
    Last Post: 06-06-2006, 12:10 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