Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Counting Customers Over Time Using DISTINCTCOUNT In Power BI

by | 9:00 am EST | December 27, 2019 | Business Intelligence, DAX, Power BI

In this blog post, I’m going to run through how you can calculate how many customers you sell to through time using a few functions such as DISTINCTCOUNT inside of Power BI. There are actually a few ways you can calculate it, which is why I wanted to create a short tutorial on it to showcase a few things. You may watch the full video of this tutorial at the bottom of this blog.

Understanding how to work around the data model that you have set up is crucial in this example, so I make sure to show you how I have set this up. It’s important because it’s not as easy as just placing a COUNT over your customer table. Remember, we want to show how the count of our customers changes over time, so we need to make sure the result will also be filtered by our date table.

## Using DISTINCTCOUNT To Count Your Customers

In this post, we are going to run through some relatively simple DAX formula. Just think about how these things are logically calculated and how DAX works with the data model. So we’ll quickly review the data model. We have the Sales table at the bottom, and the facts table on top: Dates table, Products table, Regions table and Customer table as our lookup tables. These tables are all connected.

We want to see how many customers we sell to per day. This is going to showcase a slight issue that we may have with the formula, but I’m going to show you how you can actually solve it through a couple of ways. By doing so, it will enable you to understand what’s happening in the data model.

## Formula #1: Figuring Out How Many Customers Per Day

The first thing we need to do is to look at how many customers did we actually sell to per day. What we can do is create a measure and call it Total Customers and then use DISTINCTCOUNT and Customer Name.

Logically, that’s what you think you would need to do: count up how many customers we sell to every single day. Now, the problem here is that we are getting exactly the same result for every single day.

The reason why we are getting this is because the formula is going to the Customer table and is saying what’s the distinct count of customers for every single day. The problem is that the Dates table has absolutely no relationship to the Customer table; the only relationship both of these tables have is to the Sales table. For every single filter that is placed on the Dates table, nothing is filtered in the Customer table when we run the DISTINCTCOUNT every single day, and that’s why we get exactly the same result.

If we want to look at how many customers we sold to on any particular day, we need to change this formula somewhat. We need to change it in such a way that it will look at the Sales table because that’s where the Date table is connected to. When some context is placed on a result, it is going to flow down through the relationships and it is going to flow to the table which it is connected to, which in this case is the Sales table.

## Formula #2: Creating A New Formula To Show Relationship

To achieve this, we’ll create another formula and call it Total Customers 2. We’ll use DISTINCTCOUNT again, but instead of using the Customer table, we need to find the Customer Name Index inside the Sales table.

Once we drag this new measure into the table, you’ll see how many unique Customer Name Index there are for every single day.

## Formula #3: Using Expanded Tables

So that’s how you do it in a simple way. There is actually another way to generate exactly the same result. There is never really just one way to answer in Power BI; there’s always a couple of ways – and sometimes more than that – to achieve your desired results.

Let’s create another measure and call it Total Customers 3. What we’re going to do inside of CALCULATE is to reference the first Total Customers measure that we created, the one which is just generating 50 for every single day, and then add the entire Sales table as a filter.

Once we push enter and drag it into the table, you’ll see it takes a while to calculate because the calculation is a little bit more complex. But as you can see, it is actually generating exactly the same result as the correct Total Customers 2.

That’s because of a concept called expanded tables. What the Sales table actually represents here is an entire flat file of our data model. There’s one massive flat file that includes Dates, Products, Regions, and Customers. By utilizing this technique and this formula, we’re essentially enabling DAX language to recalculate the Total Customers measure by calculating it over the entire expanded Sales table.

In the first measure, there was no connection. So we created a connection in the third measure by referencing the Sales expanded table inside of a calculate statement to generate the correct result.

## Visualizing The Data

I would generally just go for the #2 option we created so we can start visualizing it using the area chart.

We can see through time how many customers we actually sell to per day. We can also use the inbuilt analytics functions inside of Power BI to draw a trend line. We can see we’re around eight or nine customers per day on average.

Remember that this is all dynamic as well, so we can bring in an additional piece of context that we might want to add.

We have gone through a few concepts and you can use them for a range of different things; instead of customers, it could be products, regions, or salespeople. At its core, it’s understanding what DISTINCTCOUNT does and understanding the data model behind the scenes to make sure you’re calculating the correct result.

Calculate The Total Of New Clients You’re Onboarding Every Month – Advanced DAX
Power BI Data Sets: Learn How To Detect Abnormal Behavior Using DAX
Secondary Table Logic Techniques Using DAX In Power BI – Advanced DAX

## Conclusion

This technique produces interesting insights, and you could ultimately utilize and visualize this in many different ways. One way that pops to mind would be via scenarios analysis. You could see, based on forecasts that you might project forward from historical customer counts, how your results might be affected if you increase the amount of customers you sell to. You could run scenarios on this using an estimated average sale price to derive the average value per customer. Really powerful stuff. Hopefully you can see the same opportunities as me with this.

Another way you could visualize this type of insight is showing comparison through time. Maybe you want to cumulatively show the amount of customers you have sold to and compare that to last month or last quarter. You would start here and then ‘branch’ out using time intelligence functions.

I run through how to use all the time intelligence functions to complete this time comparison analysis in my Mastering DAX Calculations course.

Good luck implementing this one, and let me know if you have any thoughts in the comments section below.

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## How to Interpolate in Excel: User Guide With Examples

In data analysis, interpolation plays a crucial role in estimating values that fall between known data...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Akkio: Everything You Need to Know About It

In an era dominated by data, efficiently managing and interpreting vast amounts of information is...

## Julius AI: A Complete Guide + Use Cases Explained

With the advent of ChatGPT, automation and efficiency became a main goal for individuals and businesses...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## How to Apply the Same Formula to Multiple Cells in Excel

One of Microsoft Excel's powerful features is using formulas to do calculations and changes to data in...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...