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.
***** Related Links *****
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.
