Custom Dynamic Segmentation Using DAX In Power BI

In this blog post, I want to go over an advanced DAX pattern about how to create custom dynamic segmentation in Power BI. You can apply this great technique to many scenarios to gain meaningful insights. You may watch the full video of this tutorial at the bottom of this blog.

Dynamic groups are created when a range of different customers are segmented based on different metrics like sales, margin growth, and average profits per month.

Table of Contents

Grouping Customers Together

The goal in custom dynamic segmentation is to rank customers based on different metrics. Looking at the visualization below, you’ll see that the data is segmented into three groups – 1 to 20, 21 to 50, and “other”.

Because of these groups, you can see key clients, key products or key dimensions and really drill into that aspect in a more effective way. You can see patterns in your data. Otherwise, all that data would only look like a jumble of dots on a scatter chart without those groups.

We are also going after creating a dynamic calculation. This means that the customers are not just ranked throughout the entire data set and timeline. We’re also doing it for specific selections.

Let’s say I only want to look at the data from my customers in the western region for the first half of the year.

Once those filters are applied, you’ll see that the results are updated dynamically. It automatically ranks the selected data from 1 to 20, 21 to 50, and so on.

Once those rankings are created, you can also create a slicer out of it and drill into these specific customer groups really effectively.

The interesting thing about this filter is that none of this belongs to any existing table from the core data models, nor does it come from any database. It is generated within your Power BI model through the use of the right DAX formula.

Creating New Dynamic Groups

Here’s how you can build these groups from scratch. Remember that you’re trying to build a table that will present data the same way the table below does it, with minimum and maximum limits for each group.

I’ve limited it to 3 groups to make it easier to lay out, but depending on the need, you can actually have more.

Once those limits are set, these Custom Groups will turn into a supporting table.

As you can see, the Custom Groups do not have any relationship with other elements in your model. So we’re going to add some logic and run a DAX formula through this table. Once that logic is there, we can start filtering the results.

This is where these Segmentation Patterns come in. We’re going to use two patterns here – Profits and Margins.

Profits By Custom Grouping

We’ll start with the first pattern, which looks at the Profits by Custom Grouping.

Let’s break down this measure part by part.

Now if we have a look at our ranking, our ranking will always be done based on the Total Sales.

The interesting thing here is that although this is based on Total Sales, our output is not actually Sales. The output is going to be based on other metrics. That again shows how effective this technique is.

So instead of just using the CALCULATE function, we’re going to add a different context into the formula. Again, this is based on the fact that we’re using a supporting table.

Using the VALUES function, we’re going to evaluate each individual customer’s ranking.

We will rank each customer using the limits that we’ve set for each group. That’s why you’ll see that we’ve added the Min Rank and the Max Rank into our measure.

Now once the evaluation meets the conditions on the filters we’ve set, that’s when the results will return the Total Profits.

Margins By Custom Grouping

Now, let’s have a look at the Margins segmentation.

Again, let’s break this formula down part by part.

You’ll see that this is almost exactly the same as the Profit segmentation, only this time, we’re using the CALCULATE function for the Profit Margin.

Just like before, we’re also adding context by setting the Min Rank and the Max Rank for our Custom Groups. From there, we’ll get the results that we need.

This is where the results are broken down into different segments. The key thing to remember is that what we’re filtering here is the table we created, which is Custom Groups.

As you can see in the visualization below, this table is filtered because our Custom Group is now in our legend.

So the visualization will now show data based on the particular customers that are part of that segmented group.

Conclusion

With this technique, it’s now easy to rank data based on specific segments. Remember that this logic is applied dynamically for every selection you have. So if you want to rank data for Midwest Wholesale Sales, you need to apply that same evaluation to that specific selection as well.

This type of insight is not exactly something that will just pop out of your raw data. The beauty of dynamic segmentation is that it can also be used in different situations and different reporting applications.

All the best.

Sam