This tutorial is all about customer insights. I will demonstrate an advanced Power BI customer segmentation technique using DAX formulas and a secondary table. You may watch the full video of this tutorial at the bottom of this blog.
Identifying or segmenting customers is a technique in itself, but comparing customer segments in the current period and in a prior period is another great technique. We can get some real value out of our Power BI customer segmentation analysis with this insight.
This technique is a combination of DAX formulas and correct data modeling.
The customer segmentation or groupings we refer to here are high or top, mid, and bottom. We’ll compare each customer’s individual current and prior segmentation, and we’ll see which customers, who were once the best performing customers dropped to become some of our worst-performing customers.
We can make better decisions around sales, marketing, or advertising with this kind of customer analysis, as this is one of the real world scenarios for business environments.
Identifying Customer Groups This Year And Last Year
The specific thing that I want to show in this tutorial are the results in this table. I’ve created a list of customers who I consider top, mid, and low and I’ve worked out the customer groups by percentage.
In this table, we can see that in the Customer Group TY (this year) column, we have the top customers this year, but in the Customer Group LY (last year) column, they were in another segment or group (mid and bottom). We can see the bottom customers last year who made it to mid and top parts this year.
For example, Johnson Ltd here was bottom last year, but is a mid customer this year with 38.2% increase. Liberty Group was also a bottom last year, but it is now a top customer this year with an increase of 512.7%.
This number in itself shows something but this enables us to create much more additional logic that will purely segment customers based on them being within a specific group.
Before we get to the formula, I’ll show you quickly how I grouped these customers. I’ve created these groups based on sales percentages and so the top customers are the 80% to 100%, while the mid group is between 25% to 80%, and the bottom group is the 25%.
DAX Calculations To Show Movement Within Customer Groups
We’re using two DAX formulas here – for this year (TY) and for the prior year (LY). Let’s go through the formula for this year (Customer Group TY) first and we’ll focus specifically on the highlighted part of the formula with the CALCULATE function.
The SELECTEDVALUE function enables us to return a text value (top, mid, bottom). We identify what group the customers are in by using the FILTER function. We run this logic through every row inside the Customer Groups table that I have created.
FILTER is like an iterating function that iterates through a specific table and runs a logic at every single row. The context that evaluates to true is what is going to be retained. So in this case, we have the top, mid, and bottom values.
The logic here includes the variables (VAR) CustomerRank and TotalCustomer, which are worked out in the first part of the formula. The Low and High in the formula are columns of the Customer Groups table. So for instance, to be in the top group, the customer has to be between 80% and 100% based on sales.
So this is the customer rank calculation that is based on this year’s sales. Now if we look at the Customer Group LY formula, all that’s changed is the customer rank that is based on last year’s sales instead of this year’s sales.
It has the same logic running through exactly the same table, but it is being run based on the rank from the prior year. And this would return the group that they’re in in the prior year versus the current year.
***** Related Links*****
Segmentation Example Using Advanced DAX In Power BI
Use DAX To Segment & Group Data In Power BI
Group Customers Dynamically By Their Ranking w/RANKX In Power BI
Conclusion
This Power BI customer segmentation technique enables us to run and evaluate through a table and return a text result. We’re able to compare the customer group results of last year and this year.
It’s also great how we can use the same secondary table (Customer Groups) to run logic through. Creating secondary tables is a data modeling technique that I showcased in another tutorial.
Combining the DAX and data modeling techniques enables us to extract this valuable insight. Furthermore, we could compare quarter on quarter results as well. The same logic and technique would apply.
This is a really powerful technique that you can implement in your own Power BI customer segmentation reports.
All the best!
Sam
