+ 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
    104

    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
    2013, Office 365 ProPlus
    Posts
    4,711

    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)

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