Using Dynamic Visuals On Ranking Based Parameters In 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.

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.

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.

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

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 a visualization and then sort them by Total Profits.

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.

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

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

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

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.

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.

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

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.

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

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

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

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

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

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

Conclusion

Good luck with this one.

Cheers,

Sam

Download

Insert your email address and press Download for access to the files used in this article.

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

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

***** Related Course Modules *****
Data Visualization Tips
Advanced DAX Combinations
Financial Reporting w/ Power BI

***** Related Support Forum Posts *****
Dynamic Visuals – Number Formatting –
Passing a visual-level filter via a dynamic measure
Dynamically Change X-Axis (Yearly and Quarterly) within a Visual via Slicer
For more dynamic visuals support queries to review see here….