Dynamic Segmentation With Dynamic Parameters - Advanced Power BI & DAX Technique

Dynamic Segmentation With Dynamic Parameters – Advanced Power BI & DAX Technique

No comments

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.

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.

dynamic parameters

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.

dynamic parameters

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.

dynamic parameters

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.

dynamic parameters

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.

dynamic parameters

The first thing I’m going to do is use a formula to get my top customers into the table.

dynamic parameters

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.

dynamic parameters

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.

dynamic parameters

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.

dynamic parameters

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.

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

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

***** 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

***** Related Course Modules *****
Advanced DAX Combinations
Learning Summit Series
Budgeting & Forecasting

***** Related Support Forum Posts *****
Correct Total By Dynamic Segmentation With Dynamic Parameters
Query Based Dynamic Segmentation (No Lookup Table In Data Model)
Displaying Segment Groups
For more dynamic segmentation support queries to review see here…

Enterprise DNA Events


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.