Next Gen Data Learning – Amplify Your Skills

Blog Home

Blog

Using Dynamic Visuals On Ranking Based Parameters In Power BI

by | Business Intelligence, DAX, Power BI

Through this example I’m going to show you how you can dynamically adjust the size of your visual. And in this case, we’re going to do it via the result ranking in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

We’re going to create dynamic visuals containing our top 10 customers for specific products.

This is a really powerful technique that you can utilize in Power BI. You can create a significant amount of visualizations by using the powerful DAX formula language.

Using dynamic visuals, especially on ranking based parameters, means you can really drill into the key driver of an attribute’s performance.

You may want to isolate your top and bottom customers, or your best and worst selling products. This technique would enable you to visually showcase all of these ideas.

To make this come alive we need to use RANKX within the CALCULATE statement.

Get a good understanding of how these fit together and it will help with the more technical aspects of implementing DAX measures inside your models.

It’s where you want to get to so that you can unleash the great analytical and also visual potential within Power BI.

So let’s dive into the first step in creating dynamic visuals based on ranking in Power BI.

Creating Total Profits Measure

For this particular example, we’re going to need a Total Profits measure. But to actually create this measure, we first need to have Total Costs. 

So let’s create our Total Costs measure. We need to add some logic here so we’re going to write SUMX, then we’ll go to the sales table and then Quantity, multiply that by the Total Unit Cost.

total costs formula with sumx

Now that we have Total Costs, we can use that to create our Total Profits. So for this other measure, we just need to go Total Sales minus the Total Costs.

total profits formula

So let’s drag in our Total Profits and then add Customer Names.

table with customer names and total profits

Notice here that we did not add any additional filters on time so this table covers everything. This table just shows the Total Profits per Customer throughout time.

Then let us turn this into visualization and then sort them by Total Profits.

total profits table turned into a visualization

So now we have a graph of our customers starting from the one with the highest profit to the one with the lowest profit.

But remember that we need to show only the top 10.

Let us then create a formula that will give us the rank of each of our customers.

Using RANKX To Rank Customers Dynamically

So let’s call our new measure Customer Rank and then go RANKX. Then we’ll add ALL within the Customer Table, and then we’ll go Total Profits.

We do not need a value here but instead, we’re going to add descending.

customer rank formula with rankx for customer ranking in Power BI

If we drag this into the table, we now have the rank of all our customers.

table with customer rank

But then we still need to work on another step to isolate the top 10.

Top 10 Customer Profits

To create a table that shows only the profits of the top 10 customers, we need to create a new measure.

Let’s call it Top 10 Customer Profits.

This measure needs a bit of logic. So we go IF Customer Rank is less than or equal to 10, then that would be equal to Total Profits. If not, make that equal to blank.

top 10 customer profits formula

Now, let us create a table using this measure together with the Customer Names.

top 10 customer profits

We now have a table with only the top 10 customers. However, we need to fix a little error here.

If we take a look at the Total Profits of our new table, we’ll see that this is the total of all the profits and not just only of the top 10 customers.

top 10 customer profits table with incorrect total

So, we need to edit our Top 10 Customer Profits formula.

Let’s add IF ISFILTERED, Customer Names. That means if the Customer Name is filtered, return the profits of only the top 10.

But IF it’s not filtered, we’ll go CALCULATE, Total Profits, then TOPN and then 10 which corresponds to the top 10 customers, and then go Total Profits.

What TOPN does here is it returns a virtual table of only the top 10 customers and then sum their profits.

top 10 customer profits formula with topn

Now we have the correct total profits for our top 10 customers.

table with correct total profits

Dynamic Visuals Based On Ranking in Power BI

Since we now have a table with our top 10 customers, we can easily turn it into a visualization.

Let’s turn it into a stacked bar chart. Let’s also turn on some data labels.

top 10 customer profits table turned into bar chart for ranking in Power BI example

Remember that we are creating dynamic visuals here. So let us drag in Product Name and then add our Total Profits.

product name with total profits table to create dynamic visuals based on ranking in power bi

Then we can easily turn this new table into a donut chart.

product name with total profits visual to create dynamic visuals based on ranking in power bi

Now let’s work on the interactions of our visuals. Click Edit Interactions at the upper left portion of the screen.

edit interactions

Then click on the filter in the visual that you want to be impacted.

clicking on filter

With that, if we click on Product 1 in our donut chart, our bar chart will show the top 10 customers for this product.

product 1 and the top 10 customers

If we click on Product 7, our bar chart will then change to show the top 10 customers for this product.

product 7 and the top10 customers

***** Related Links *****
Creating Dynamic Ranking Tables Using RANKX In Power BI
Creating Multi Threaded Dynamic Visuals – Advanced Power BI Technique
The Ultimate Advanced Viz. Technique in Power BI – Multi Measure Dynamic Visuals

Conclusion

Good luck with this one.

Cheers,

Sam

[youtube https://www.youtube.com/watch?v=HJdVfYkfhmE?rel=0&w=784&h=441]

Related Posts