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

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.

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

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

Now we can see our clients’ ranking.

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.

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

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

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.

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.

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

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## How To Merge Queries In Power BI

Merging is another powerful transformation to optimize our...

## Unpacking The Evolution Of Power BI – From Launch to Now

Microsoft Power BI is a dynamic business intelligence platform that has revolutionized the way...

## Finding Patterns In Your Data Using Internal Logic in Power BI

Today I’m going to showcase how you can discover patterns in your data when using Power BI. This is an...

## Report Builder Tutorial: Sort & Filter Data In Paginated Reports

In this Report Builder tutorial, you’ll learn how to sort and filter the data in your paginated...

## Creating A Pareto Chart In Power BI – Advanced DAX

This blog tutorial showcases a really interesting example around Pareto analysis. I’m going to teach...

## Power BI Tooltips On Report Images

With the recent Power BI challenges that we run in Enterprise DNA, I've become a huge fan of the Power...

## Best Practices In Using The Q&A Feature In Power BI

The Q&A feature or the natural language feature is seriously one of the most incredible features...

## Perpetual Holiday Calendar – POTW #12 (Power Query Solution)

In this blog post, we'll explore my solution to Problem of the Week #12. For this challenge, we...

## What Is DAX Studio In Power BI Desktop

This tutorial will discuss what DAX Studio is and how it can be used to optimize your DAX codes. DAX...

## DAX Queries Using Variables And Important Functions

This tutorial will discuss about variables, including the FILTER and CALCULATE functions. You’ll...

## Dynamic Tooltip In Power BI With Embedded Charts

I want to show you an awesome trick for the visualizations in your reports. You can use a dynamic...

## Power Query Features: An Overview

This tutorial will discuss about the available features inside the Power Query Editor. You'll learn how...