Using Dynamic Visuals On Ranking Based Parameters In Power BI

5 comments

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.

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

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

Conclusion

In this post, we discussed how to dynamically adjust the size of visuals based on ranking in Power BI.

We started with creating our Total Profits measure then we created a formula that includes RANKX so that we can have the ranking of our customers dynamically.

Next, we created a new measure called Top 10 Customer Profits which includes some logic so that we can actually isolate our top 10 customers.

Having the measures and tables needed, we easily got our dynamic visuals and were able to show the top 10 customers per product.

I hope you enjoyed going through this post and that you now see how you could integrate this technique into your own models effectively.

Good luck with this one.

Cheers,

Sam

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

membership banner 3

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

Enterprise DNA Events

5 comments on “Using Dynamic Visuals On Ranking Based Parameters In Power BI”

  1. More great info Sam! Is there a way to simply add a column to a table that represents each row number in the table? In other words, the value is NOT related to any table data and always returns the same results regardless of filtered dimensions, specific column sort ordering, etc. It is simply the number of the row as it is rendered.

      1. Thanks for the prompt response. Actually, an index column doesn’t work for this purpose because it is specific to the record (row). The column value should ALWAYS equal the row number of the record in the table, regardless of how the table is sorted/filtered, etc. (like the ROWNUMBER function in Excel?)

        I’ll keep trying on my own – I’m sure you’re busy enough! Thanks again!

        Dave

  2. Hi Sam
    I wrote these measure but my result is 1 for every row while in your demo file this measure work correctly.

    ranking = RANKX(ALL(‘Product’),SUM(Sales[Sales]),,DESC)

    do you know why?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.