I’m going to talk about dynamic segmentation using dynamic parameters because I’ve seen members ask about it so many times on the Enterprise DNA support forum. You may watch the full video of this tutorial at the bottom of this blog.
What’s challenging about this is figuring out which areas to segment first. You would also have to find a way to feed dynamic parameters into your formula. I’m going to simplify all that through the example I’m going to show here.
Creating A What-If Parameter
The first thing I need would be a parameter. In this case, I created a Customer Rank parameter.
I created this parameter by going into Modeling, then by clicking on New Parameter.
A what-if parameter would automatically create a table for me. It also creates a measure for the selection I made.
When I drag that into my report, it’s going to show whatever selection I make in the slicer.
The key thing to remember here is that your parameters should split the results every time you do a custom segmentation.
So aside from the Customer Rank parameter, I also have information on areas where the stores are found.
The context of the calculation is one of the key factors in getting this right. There’s also additional context down here showing where the stores are.
Now I want to separate my revenue by comparing my top customers and my bottom customers. I also want both these top and bottom groups to be dynamic. This is where dynamic parameters come into the picture.
Applying Dynamic Parameters
So the results have to change depending on how I want to look at the data. This means that if I change the slicer for Customer Rank, the data on the table should change as well.
The first thing I’m going to do is use a formula to get my top customers into the table.
I created the column by using the CALCULATE function for the Total Revenue and then using TOPN as a ranking function. TOPN also returns a virtual table based on ranking results.
I added context here as we calculate the Total Revenue because I’m only looking at the top ranked customers. That ranking is determined by the Customer Rank Value.
By using TOPN, I’m going to work through all the customer data. However, it would only maintain the context for the top 4, since that’s the parameter being set in the table. It’s the value I’ve set in the slicer.
I can move my slicer to 3, and the results would also have to change and reflect only the data for the top 3.
I also want to look at my bottom customers, so I’m going to add that into my table.
Since I put my bottom customers there, it’s also important to note that I would need the total number of customers per store within any given time frame.
To get the Total Customers, I used the COUNTROWS function.
Note that this is all just sample data, so the numbers I come up with from this formula might seem small and unrealistic. But this calculation would also work for bigger frequencies once it is applied to real-life scenarios.
Here’s the formula I used to get the bottom customers. Notice that it’s very similar to the one I used for Top Customers, but with some minor adjustments.
I used both the CALCULATE and the TOPN functions here as well. But instead of just putting in the Customer Rank, I used the difference between the Total Customers and the Customer Rank Value.
Then instead of just placing VALUES and Customer Names, I had to add logic. Since I’m getting the bottom customers instead of the top ones, I’m going to use ASC instead of DESC.
The previous formula iterated through every single customer to get the top ones. But since I’m getting the bottom customers, this means that it would also count the instances where there were zero sales.
I have to make sure that the ones counted as the bottom customer also made a purchase. This is why I’ve added that the value should be greater than 0.
Because of that, my data now shows how many customers actually made a purchase. On this first row, it shows that 11 customers bought something in that specific store within the given time frame.
It can get quite tricky at this part, mostly because TOPN is already creating a virtual table, but I’m also creating another virtual table within it. This virtual table looks at a much smaller subset of customers, and not at the customers as a whole.
So I’m doing another ranking here at the end of the formula for Total Revenue. And as I mentioned earlier, I’m using ASC instead of DESC.
With these formulas, I now have dynamic parameters added into the dynamic segmentation.
Visualizing The Results
The technique I used could potentially be used for much larger data sets. This means that to better understand the results, it’s useful to have some visualizations to showcase them.
I’ll start off with a simple chart that can show you a comparison of the top versus bottom customers for each store.
I can also change this up and use a stacked bar chart in case the numbers are large enough and would be better displayed that way.
With these visualizations, I can get a better insight into the numbers. This, for example, shows which stores are at a higher risk. I can see which stores have very few customers resulting in low revenue.
***** Related Links *****
Dynamic Segmentation: How To Segment Customers Into Groups Using Advanced DAX
Customer Segmentation Techniques Using The Data Model – Power BI & DAX
Segmentation Example Using Advanced DAX in Power BI
Conclusion
The data I used here is very low frequency, but imagine the possibilities when this is applied to bigger data sets. This technique can also segment other information and add so many different parameters aside from just top and bottom customers, or store locations.
This example shows how to work with measures inside measures as well. Techniques like these give amazing insight into data, regardless of where it is applied.
All the best,
Sam
[youtube https://youtu.be/EAbCHqMRE5Y?rel=0&w=784&h=441]
