By 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
***** 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….
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.
You can add an index column for each row inside the query editor. I’m pretty sure that will achieve what you are after.
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
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?
Try placing a CALCULATE around your SUM function or alternatively use a Measure there.