**Bottom Line:** Learn two ways to solve the data analysis challenge, calculating distinct count, with pivot tables.

**Skill Level:** Intermediate

## Video Tutorial

## Download the Excel File

I've included both the original file and the solution file for you to download here:

## Counting Unique Rows

In this post, we're going to take a look at **two different ways to do a distinct count using pivot tables**. These two methods were submitted as solutions to the **data analysis challenge** that you can find here:

To summarize the challenge, we want to create a **summary report of deal count by stage**, but there are multiple rows per deal in the CRM data. So we have to find a way to **create a distinct count **(counting unique rows) for each deal so that we can sum them up.

By the way, **thank you** to anyone who submitted a solution to the data challenge! There were a lot of great submissions.

## Solution #1 – Using a Helper Column

The great thing about this solution is that it can be used in **any version of Excel**.

Start by turning your data into an **Excel Table**. To do that, just select any cell in the data set, and click on **Format as Table** on the **Home** tab. Right-click on the table format you want and select **Apply and Clear Formatting**.

Hit **OK** when the **Format as Table **window appears.

Now that your data is in **Table format**, add a **helper column **to the right of the table and label it **Deal Count**. Use the** COUNTIF function**, with the **range** being the **Deal ID** column, and the **criteria** being the **cell in the Deal ID column that corresponds with the row you are in**.

The formula will return the **number of rows** **for each Deal ID number**. If we divide the formula into the number 1, we will get fractions in each of those cells that when **added together** will count **one entry for each deal**.

The change to the formula can be seen in green here:

=**1/**COUNTIF([Deal ID],[@[Deal ID]])

Now that we have these fractions that will give us a **distinct count **when we create our pivot table, we can go ahead and **create the pivot table** by choosing **Pivot Table** on the **Insert** tab.

To create our **summary report** using the new pivot table, put the **Sales Stage** in the **Rows** area and **Deal Count** in the **Sum of Values** area.

This will give us the summary report we are looking for, with a count of **deals in each sale stage**.

The nice thing about using a pivot table is that as we **add or delete source data entries**, we can **refresh the pivot table** ( `Alt` + `F5` ) to include those changes.

## Solution # 2 – Using Power Pivot

This solution is only available for** versions of Excel that are 2013 or later for Windows**.

We still want our data formatted as an **Excel Table**, but we **don't need a helper column** for this solution.

This time, when we create our pivot table, we are going to check the box that says **Add this table to the Data Model**. (Data Model is another term for** PowerPivot**.)

When you build your pivot table this time, you are going to drag **Deal ID **to the **Sum of Values** area.

That initially gives us numbers **we don't want** in our summary report. To fix this, we want to right-click on the **Sum of Deal ID** column header and select **Value Field Settings**. This will open a window where we can choose **Distinct Count** as a calculation type.

The **Distinct Count function** goes through the Deal ID column and gives us a count of the **unique values**, so our summary report will look just like it did for Solution #1.

## Comparing the Two Solutions

Both of these solutions are great because **they can be refreshed when new data is added **to the source table.

The advantage to Solution #1 is that it can be done in **any version of Excel**. With that said, if you are running 2013 or later in Windows, **Solution #2 is the superior option**. This is because Solution #1 gets wonky when you try to filter the data down (say, for a certain product) or use slicers to dissect the data further.

If you'd like to** learn more about using Pivot Tables**, I have a separate blog post you can check out here: Introduction to Pivot Tables and Dashboards.

## Other Solutions

There were lots of **other great solutions **to the challenge that were submitted. They included using **Power Query **and **new dynamic functions**. We will take a look at those in future posts, but I wanted to start with these two because they were more universal in terms of Excel version access.

If you have questions about either of these solutions, please leave a comment below!

What if there are more than one if condition? If you do it by Product type and by region, what do you do in Power Pivot?

Hi JP,

Great question! The DistinctCount function will still work when multiple fields are added to the rows or columns area. You do NOT need to make any changes to the calculation.

If you were to add the Product Name to the rows area above Sales Stage, then you would see the number of deals in each stage for each product. The Grand Total row would still show the total number of unique deals, 14 (after the new data is added).

You can continue to add additional fields to the rows and columns areas and the DistinctCount function will calculate the correct results for each cell in the values area based on the filter context of the pivot table.

I hope that helps. Thanks again! 🙂

I notice that every Deal has a common outcome for all products.

What if there were products that were in the tender that were different.

e.g. Product A,C,D,E = Win but Product B = Loss

Great video here. Much appreciated.

I have been using an if function to calculate this, steps below;

1. Arrange the ID column in either ascending or descending order

2. Add a new column called “UniqueID”

3. In the UniqueID enter formula below;

“IF(A4=A3,0,1) then run it down

4. Create a pivot table to sum the UniqueID column, this will sum only the 1’s which are unique

Sorry, iam not able to attach anything on this chat area

My Excel (365) doesn’t have “Distinct Count” in the menu of Value Field Setting. Therefore, I can’t create the Povit Table as you did here in the video. My Excel is new version of Office 365. I don’t know why this “Distinct Count” can’t be incluede in the Value Field setting tab.

Don’t forget to check the “Add this Data to Data Model” option when loading/creating the Pivot Table. If you don’t do that you won’t see the “Distinct Count” Field option

I don’t see why it needs to be added to the Data Model? Won’t it still work if it’s not added to the Data Model?

To get distinct count in the value field option you need to add a data model.

this is great feature highlight to me and helped me 99%. i am stuck with one problem as the distinct count is also counting “0”. how to exclude “0” in distinct count using the pivot?

This was exactly what I needed! Thank you!

Really great and neat solution!

Applying solution 1 was fairly easy in my case, because the source data is available and maintainable. However, it’s terrible that such a simple thing as a pivot row count has to be so complicated (why not available in the pivot!). I almost decided to throw it in the Data model, but I couldn’t believe I had to, just for the row count.

Very helpful. Explained in a simple nice way. Thanks alot.

Very Helpful! I already create a pivot table but It seems that I had issues with duplicate data so is possible to add distinct count in an exiting pivot table or I need to create a new one? Thanks

i cant check the “Add this Data to Data Model” option when creating the Pivot Table. How do i fix this?

If you’re using Mac, the excel Mac version doesn’t have Power Pivot.

I went and used the Data Model version, and all the execs use Macs. So just a heads up to anyone – Data Model (Power Pivot) version will NOT work in a Mac if you are using slicers.

Keep that in mind. I have to start all over again.

If I’m using distinct count in a power pivot table, is there away to filter out items with $0 value?

Hi Jon, thanks for your help!

=1/COUNTIF([Deal ID],[@[Deal ID]])

-> I’m trying to apply the formula to my table but it accepts to enter only the first part of the column header *[Deal ID]*

For the second part *@[Deal ID]*, it shows error.

Do you have a clue why?

I have created a table with my data as you mention, I have replaced gaps with 0 and no empty column headers.

Still error with the second part of your function.

PS. Mac user, so no Power Pivot option.

Thanks

Aggelos

There is a BIG issue using the the Data Model. It works fine until you need to adjust the range of your data. Just try it !

Thanks 🙂

Add to Data Model (option 2) was exactly what I needed – thank you so much!

Hi there! Thanks for this great video. I have been looking for an answer to an issue I am having with updating data in a pivot table that uses the distinct count while using the data model. When new data is added and I go to refresh the pivot (change data source to include data that has been appended to the bottom) the picot table disappears, leaving behind the blank spot to build the report and blank field list. Any chance you have an explanation/fix for this? TIA,