• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PowerPivot and DISTINCTCOUNT

txfrazier

New Member
Hi,
I have a a column in powerpivot where I want to count the number of distinct entries but exclude entries that are blank in the count. The formula in the calculated field is:
UniqueIDProcessID Records:=DISTINCTCOUNT([UniqueIDProcessID])

For example: I know I have three unique records given a set of conditions in other columns, however the count shows 4 because it is including Blank as a distinct record.

Thanks for your help
 
I don't have PowerPivot on my machine, so just guessing, but can you do something like this?
=DISTINCTCOUNT([YourRange])-COUNTBLANK([YourRange])
 
Luke, thanks for the suggestion. I tried it but got an error that a circular dependency was detected. Even without the error, I don't think counting the blank etnries would work since I have quite a few and would have resulted in a negative number.
 
Hmm, good point. Don't know about the circular error, but to at least correct the math, could change formula to:
=DISTINCTCOUNT([YourRange])-(COUNTBLANK([YourRange])>0)
By changing the last bit into a Boolean check, we end up subtracting either 1 or 0 (True or False), preventing a negative count. Hope someone else with PP experience can shed some more light. :(
 
Back
Top