Change Your Visuals Size By Ranking Logic in Power BI using DAX

Have you ever wanted to create a visualization which changes based on any ranking selection you could make? In this video, I show you exactly how to do it.

Think of a scenario where you want to showcase your top 20 clients. Pretend you’re in a meeting saying, “These are the clients that are contributing the most to our profitability out of all our sales.”

But then you decide, “Well, let’s maybe narrow down the search to our top 5 clients.”

Being able to dynamically select a slicer that changes the chart or visualization that you’re looking at, to a more specific insight based on ranking, is a very powerful visualization option.

First off, we need to start with our core calculations, like total sales or total profits. After we’ve created that measure, we’re going to have some ranking logic overlaid on a supporting table that we create. There’s a little bit to set up here but I show you exactly what to do.

The supporting table is going to have the selections of ranking numbers for whatever ranking that we might want to select. So it could be top 3, it could be top 5, it could be top 20.

Once we’ve got this table and a way to extract any selections that we could make inside a measure, we can then filter any calculation, like total sales or total profits, or margins by that ranking selection.

Once we’ve done all this, we can dynamically drill into any specific insight based on the current ranking (in this case of our customers). There’s seriously so many different ways that you could utilize this technique in your models. You could use it on your products, regions, salespeople or any other filter or dimension you have inside your data model.

It’s a matter of just trialing it out, seeing how it works within your current models and then trying to embed the technique into your mind so that you can very quickly see how it could be used in other ways.

I use this very same technique in many of the Power BI models at the Enterprise DNA Showcase. Check these out if you have some time, and really try to imagine what’s happening underneath ‘the hood’ of these models – you will learn a lot.

The key there is understanding RANKX very well. You need to make sure that the context adjust correctly inside the table function part of the RANKX formula. Without knowing the exact details it difficult to answer precisely, but my recommendation is to really explore RANKX more. I also explained this very technique in session 1 of the last Enterprise DNA summit. Here’s a link to the video – https://www.youtube.com/watch?v=5DYYUCsxUmg