Distinct Count & Blanks – Power Pivot Real Life Example

Posted on February 11th, 2013 in Power Pivot - 3 comments

When it comes to analyzing business data, managers are always asking, “so how many distinct x each y is doing?”

And that sends us, data analysts & reporting professionals running from pillar to post figuring out the best way to do it.

  • We can use variations of SUMPRODUCT, COUNTIFS etc, but the methods are not flexible..
  • We can use VBA, but it would become slow as you add more data.
  • We can use Pivot tables, but it only gives half of what we want ie each y part, but not distinct count of x.
  • We might as well shave our head with a shovel before manually counting values.

And that brings us to 2 distinctly simple solutions:

  • Using Power Pivot & Excel 2010
  • Using regular pivot tables in Excel 2013

Today, lets talk about these 2 approaches & see why they are so better than anything else for distinct count situations.

Distinct count problem faced by Joanne:

Joanne, one our readers sent me this email few days ago,

Here is something that I thought your readers might be interested in, as a real life business example. I always have the need for unique counts in my Excel sheets and was elated to find out that MS had built this function into 2013 pivot tables. I generally use SUMPRODUCT to get to my answer, but some of my files take hours to calculate with this formula. Since I am still waiting for my 2013 CD to arrive, I took advantage of the addin to 2010 to play around with this new technology. At first, I was very excited at the ease of use to get to a unique number. But then looking into the results a little more, I found out it was skewed. Here is what I mean.

This is my data, simplified a little and any confidential information has been taken out. The last two columns are “helper” columns for my pivot. They basically return the agreement number if the proposal type is a certain value. If it doesn’t find a match, it leaves it blank. Data is sorted only so its easier to see how the pivot tables arrive at the answers.

Distinct count using Excel Power Pivot - Sample data

The blank is what messes it up. Doing a simple pivot with DistinctCount, the numbers are definitely wrong. Its counting the blank fields as one of the items, so everything increases by one. The Grand Total is correct, sort of, but its misleading. There are 4 unique MBP accounts, 3 with actual agreement numbers and one blank, but looking at the numbers in the column, it doesn’t add up to 4 visually.

Distinct count using Excel Power Pivot - including blanks

So there was my challenge, to get a DistinctCount, but ignoring all the blank fields within the data set. Using a New Measure, I was able to create a formula for the pivot that ignored the blank cells. It’s a little complex, but what good Excel formula isn’t? Once that was created, the pivot worked and the grand totals are more accurate. I did try using the DistintCount-1, but it didn’t come to the same answer. When it did work and the total was 1, it showed zero, which is correct. But I like the clean look below of showing nothing, thus not tempting the user to click on the zero.

MBP formula: calculate(distinctcount(('Implemented'[MBP Accounts])), filter(Implemented,Not(isblank('Implemented'[MBP Accounts]))))

Distinct count using Excel Power Pivot - excluding blanks

Distinct Count Problem & various solutions

I think Joanne’s measure for Distinct count is excellent. It shows how easily we can calculate anything we want using Power Pivot.

But do you know we can modify it and use various other solutions as well?

In the below video, I have explained 3 different solutions and how they fare.

  1. Joanne’s original CALCULATE (… FILTER(…)) solution
  2. Improved CALCULATE(…) solution
  3. Using Excel 2013 regular pivot tables to calculate DISTINCT Counts

Watch the Distinct Count using Power Pivot & Pivot tables video [17 min]

Go ahead and watch this video. This is just a sample of what you get when you join our Power Pivot course.

Distinct Count calculations using Power Pivot for Excel & more

Download Example workbooks:

Click below links to download example workbooks shown in this lesson:

Thank you Joanne

Thank you so much Joanne for sharing this example with us. It was fun learning from your example & exploring alternative solutions.

If you enjoyed this example, say thanks to Joanne.

Want to learn more? Join our Power Pivot Classes

If you would like to learn more about Power Pivot, then please consider joining my new course – Power Pivot online classes. The objective of this class is to make you a data analysis & dashboarding monster.

We are in enrollment stage now and we will be starting online classes from 18th of February. All classes will be pre-recorded so that you can watch them at your own time.

Click here to learn more & join us.

Online Power Pivot & Excel Dashboard classes from Chandoo.org

Introducing our Online Power BI Class:

Introducing Online Power BI Training from chandoo.org - check it out today

Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

Click here to know more and join us.

Written by Chandoo
Tags: , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

3 Responses to “Distinct Count & Blanks – Power Pivot Real Life Example”

  1. Nikolay says:

    If you do the text to columns function (this way the pivot table treats the blanks as blanks), on the data and pivot it afterwords you get the right results. No need for the complex formulas

  2. Colin says:

    i think we're trying to find a way to leave the formula(ae) intact and not count blanks. the only way text-to-columns works is if you turn the offending cell(s) into text first, which defeats the purpose.

Leave a Reply