Ranking Visualization In Power BI – Dynamic Visual

In this blog post, I want to teach you how to show your customers moving through groups over time in a dynamic way. You can do this ranking visualization in Power BI using a unique slicer. You may watch the full video of this tutorial at the bottom of this blog.

When I click This Quarter, I want to see who my Top 20 clients are within this ranking visualization. Then when I click on the Previous Quarter and Two Quarters Ago slicers, I also want to see who were my Top 20 clients for those time frames. This, for me, is a really compelling ranking in Power BI visual that was not possible not too long ago until Power BI enabled conditional formatting within the charts.

The first thing we need to do is to create the underlying calculations. Since we already have the calculation for our revenue from This Quarter, we need to create the revenue calculations for the Previous Quarter and Two Quarters Ago.

Calculating The Revenue From Last Quarter

I’ll create a new measure and name it as Revenue LQ. We’ll use the CALCULATE function, and reference the Total Revenue. Then use the DATEADD function, reference the Date table column, and then write minus 1 quarter.

ranking in Power BI

The DATEADD function is such a good formula to use because of its flexibility to quickly change to any timeframe.

Calculating The Revenue From Two Quarters Ago

I’m going to copy this measure and paste it in the formula bar to create a new measure. Let’s call this one Revenue 2 Qs Ago, and then change the quarter from -1 to -2.

ranking in Power BI

So now we’ve got all the revenues from the previous quarters. Let me just show you the revenue from last quarter.

ranking in Power BI

Calculating The Ranking Of Clients

The next step is to create a ranking formula that indicates how a client is ranked. We’ll create a new measure and call it Revenue Period Ranking.

To start things off, I’m going to use the RANKX function and reference ALL of my customers. Then I’ll write an expression, which is Total Revenue, and put it in a descending order.

ranking in Power BI

Now we can see our clients’ ranking.

ranking in Power BI

I also want to embed the ranking measure we’ve made into the Total Revenue by Customer Names visualization in the left so I’m going to use some variables.

I’m going with VAR then CurrentPeriodRank. The great thing about variables is that you can name them whatever you like.

ranking in Power BI

Then I’ll copy this down twice in the formula bar and change their names to LastPeriodRank and TwoAgoPeriodRank.

ranking in Power BI

Then I’m going to input the Revenue LQ and Revenue 2 Qs Ago calculations that we just did.

ranking in Power BI

The next step is to match these up to whatever is selected in the slicer. We’ll use RETURN and then make a SWITCH TRUE statement.

ranking in Power BI

We’ll use the SELECTEDVALUE function and reference Time Frame.

If the time frame is equal to this period, we want that to equal to CurrentPeriodRank.

At this point, I’m just bringing a lot of elements together here. I’m going to copy this portion of the formula twice.

I’m going to change This Period to This Quarter, Previous Quarter, and 2 Quarters Ago.

The key here is that no matter what selection we make in the Time Frame slicer, the numbers under the Total Revenue column shouldn’t change.

With Q2 2018 as the selected filter for Quarter & Year, this is the Total Revenue value for our customers and their ranking for This Quarter.

But we also want to see how these people ranked in the previous quarters. To do this, we just need to select the Previous Quarter and Two Quarters Ago from the slicer. Then the ranking number is going to change for each corresponding customer.

Using Conditional Formatting For The Ranking Visualization

The last thing we need to do is go into the Data Color section, and then go to the Conditional Formatting area.

We’ll to the Rules section and then find the measure Revenue Period Ranking.

If the value of the number is greater than or equal to 1 and is less than or equal to 20, we want it equal to the darker blue color.

Let’s create a new rule. If it is greater than 20 and less than 20,000, then we want this to be equal to a much lighter blue color.

This is going to highlight the Total Revenue by Customer Names visual.

***** Related Links *****
Power BI Conditional Formatting For Chart Visuals – What’s Possible?
Showcase Unique Insights Using Conditional Formatting In Power BI
Custom Conditional Formatting Techniques In Power BI

Conclusion

To come up with this group ranking visualization in Power BI report, we used a couple of different techniques. We have a supporting table which determined the metric we’re looking at. Then within the measure itself, we used logic derived from a variable. And then to finish it off, we used conditional formatting within the visual itself to showcase some interesting insight.

I hope that with this tutorial, you’ll be inspired on how you can carry out these techniques in your own reports. There are so many different ways that you can use logic like this. That’s definitely the key takeaway that I want you to learn from this tutorial.

If you liked this content, then watch the accompanying video and don’t forget to subscribe to Enterprise DNA TV.

Cheers,

Sam

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