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

4 comments

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.

Good luck with this one.

***** Learning Power BI? *****
All Enterprise DNA TV Resources
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – Power BI Resources

4 comments on “Change Your Visuals Size By Ranking Logic in Power BI using DAX”

  1. Hi sam
    I applied the technic and i want to thank you, for sharing this.

    I’m have another question to see if you can help me.

    I have my sales segmenting in
    different distribution channels, if i filter the top 20 , how i can do that went i filter one channel in the table only shows the top 20 clients of that single channel.

    Thanks

    Like

    1. 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

      Like

  2. Nice Video, Sam the only think the total doesn’t match the ranked amounts though, it just shows the total for ALL sales…Is there a way to just total the selected ranked items?

    Like

    1. That’s a good point. Here’s one way you can do it

      Sales by Customer =
      VAR RankedSales = CALCULATE( [Total Sales], TOPN( [Rank Select], Customer, [Total Sales], ASC ) )

      RETURN
      IF( HASONEVALUE( Customer[Customer Names] ),
      IF( [Customer Ranking] <= [Rank Select], [Total Sales], BLANK() ),
      RankedSales )

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s