Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Distinct Count in pivot table

Latest post Sat, Jun 28 2008 4:54 AM by Nick Hodge. 1 replies.
  • Fri, Jun 27 2008 4:07 PM

    • Pamd
    • Not Ranked
    • Joined on Fri, Jun 27 2008
    • Posts 1
    • Points 21

    Distinct Count in pivot table

     Hi,

    I am new to Pivot Tables. I need to export a BO report into excel and it should have pivot cabability. The data that I have in the report is

    Org Second & Org Third are on the page level. Act_code, Prg_code are on the row level. Hours_charged is on the data level. I need to also show a distinct count of employees for each Org second and if you expand it and select the Org third then it should reflect the changes. I am getting the correct value for the hours_charged, but I am not getting it for Count of employees. I need a distinct count group by Org second. Any help will be appreciated.

    Thank you,

    PamD

     

    • Post Points: 21
  • Sat, Jun 28 2008 4:54 AM In reply to

    • Nick Hodge
    • Top 10 Contributor
    • Joined on Sun, Dec 23 2007
    • Southampton
    • Posts 543
    • Points 9,431
    • MVP

    Re: Distinct Count in pivot table

    Pam

    COUNT(DISTINCT(...)) is not currently possible in a pivottable field you will need to do that in the database. If your data is pivotted from a flat table already in Excel though, there is some instruction on setting up this column on Debra Dalgleish's site

    http://www.contextures.com/xlPivot07.html#Unique

    Regards
    Nick Hodge
    Microsoft MVP, Excel
    Southampton, UK

    Filed under:
    • Post Points: 5
Page 1 of 1 (2 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.