Segmenting Dimensions Based On Ranking – Advanced Analytical Technique For Power BI

In this example, we’re going to get quite advanced in Power BI using DAX. We are going to focus on segmenting dimensions based on its ranking on that particular table. You may watch the full video of this tutorial at the bottom of this blog.

This is an amazing concept of how you can utilize calculated columns within Power BI. This can bring additional insights that would have never been possible with traditional tools like Excel.

Utilizing these techniques effectively and then implementing great visualizations around them, which I will also dive into, can really showcase your data in a compelling way.

Visualization Of Segmented Data

I’ve always talked about the amazing analytical power of Power BI but you won’t see this level of analysis if you’re using DAX formulas one by one. Its true power comes out when you’re using a combination of different formulas and techniques stacked together.

That’s what I’m going to show you through the visualization below.

segmenting dimensions

Again, the data shown here cannot be analyzed as deeply as this if you don’t layer multiple formulas. I call this technique measure branching. To add to that, I also overlaid some calculated column logic to break down the results further. It’s the best way to get a really great insight in a case like this.

In this scatter chart found in the lower right part of the report, for example, you’ll see that there are different colors representing different data groups. In this case, the dots represent cities, with each color showing you the best, the worst, and the ones whose rankings are just okay.

segmenting dimensions

You’ll also see that our y axis shows the quarter-on-quarter margin change. The x axis, on the other hand, shows quarter-on-quarter sales change.

segmenting dimensions

This is the type of insight we get when we segment our data by measure branching. It shows you whether a large increase in sales was derived from a big increase in margin, and vice versa. It shows you the correlation among these data points.

Of course, this is just a demo data set so you won’t see a huge cluster. But once you apply this technique to your own data, you can get even more advanced insights.

With all these formula and techniques combined, you’ll also notice a difference in the overall look and feel of the visualization. I’ll show you what I mean by removing the legend for City Group.

segmenting dimensions

Note that I created the City Group legend using calculated column logic. So when I remove that legend, the way the data is showcased changes. It doesn’t bring as much insight as we need.

segmenting dimensions

All we see now are general data points. It doesn’t show us where our worst or best clients are. That alone takes out a whole lot of insight we should be getting.

We see now that these additional layers of logic actually allow us to see things in a much better light.

Now, let me drag City Group back as a legend.

segmenting dimensions

As you can see, the visualization greatly improves.

Segmenting And Ranking Your Data

This time, I want to show you how I segmented these groups of customers to create that legend.

Inside the calculated column, I used RANKX to get the rank of each city based on the dimensions given. In this case, we’re ranking them based on sales.

In the formula, I included ALL the Regions in our table and their Total Sales.

RANKX is extremely helpful here because it gives us a way to organize all that data and gain some insight into them.

We can check not only which cities are doing well, but also which ones are not performing as well, which is why they are found way down at the bottom of the column.

This function is also the foundation of the next logic I’m going to use.

So on top of the RANKX, I also used SWITCH TRUE to create an additional dimension.

With that additional layer, I can see now what my best performing cities are, as well as the worst performing ones. So, if the City Sales Rank is lower than or equal to 10, they are tagged as part of the “Best”.

This is a really great technique you can utilize to create more ways to segment your data. Note that this is not only useful in a scatter chart. This can also be applied to different types of visualization.

For example, you can also use it in a donut chart as shown below.

You’ll also see that the donut chart and the scatter chart move alongside each other. So if I click on the part that represents the Worst numbers on the donut chart, the scatter chart adjusts as well.

This allows us to zero in on subsets of data or other dimensions.

***** Related Links *****
Customer Segmentation Techniques Using The Data Model – Power BI & DAX
Use DAX To Segment & Group Data In Power BI
Data Segmentation Based On Percentage Groups – Advanced DAX In Power BI


Try diving into these techniques and implementing some of them into your own models. I’m confident that you’ll see good results and you’ll be happy with the results in terms of what you’ll get in your reports and dashboard.

Hopefully you can see how combining these different techniques can get you some really advanced levels of analysis. Doing this on Power BI can give you really detailed reports and high-quality visualizations just like the one in our example.

All the best.


Enterprise DNA Power BI On-Demand

The Latest

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and…

Python Inline If: Simplify Your Conditional Expressions

You’ve been cruising through your Python journey, slicing through lists, taming those wild tuples, and maybe even wrestling with a…

Python Empty String: Understanding and Handling It Effectively

Power BI financial dashboards provide a quick and easy way to monitor an organization’s financial performance in real-time. By consolidating…

Power BI Financial Dashboard Examples: Key Insights for Businesses

When working with integers in Python, you should know the maximum value your code can handle. This will depend on…

Python Max Int: Understanding Maximum Integer Limits

Pi is a fundamental mathematical constant that represents the ratio of a circle’s circumference to its diameter. Leveraging Pi in…

4 Ways to Use Pi in Python With Examples

ChatGPT is an advanced AI-powered tool that can transform the way you write code. Developed by OpenAI, ChatGPT accelerates your…

ChatGPT for Coding: A Guide With Practical Examples

When working with data projects in Python, you are most likely to use datetime functions. Almost every dataset that you’ll…

Python datetime, a comprehensive guide with examples

Power BI is a powerful business analytics tool that helps you visualize and analyze data from various sources. One of…

Power BI Themes: How to Customize Your Reports with Ease

With the advent of ChatGPT, individuals and businesses worldwide have been using it to simplify their daily tasks and boost…

ChatGPT for Data Scientists: Unleashing AI-driven Insights

Staying ahead of the curve in data analysis is essential to your success in business. One of the most innovative…

ChatGPT for Data Analysts: Revolutionizing Insights and Reporting

Imagine being able to look at your data from every which way — from the bird’s eye view right down…

Power BI Hierarchy: Unlocking Levels and Drill-Downs in Visualizations

As you explore Python and take on more complex projects, you’ll notice that some projects have lots of files. Managing…

os.path.join: Simplifying File Path Operations in Python

Load More