You are reading the article Creating Dynamic Ranking Tables Using Rankx In Power Bi updated in December 2023 on the website Hatcungthantuong.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 Creating Dynamic Ranking Tables Using Rankx In Power Bi
In this tutorial, we’ll look at a unique technique inside Power BI that allows us to create dynamic ranking tables using the RANKX function. You may watch the full video of this tutorial at the bottom of this blog.
What are Dynamic Ranking Tables?
Dynamic Ranking Tables showcase a range of information dynamically in a table, but the information shown is based off a specific ranking on a singular calculation.
We can look at the Top 20 Customers, based on Sales, across a range of other metrics, like Profits, Margin, Growth, etc. This technique will also automatically update the table after any change of context to the report page.
Let’s have a look at the Top 20 Customers in the table.
You can see the Profit Growth Year On Year table which requires the development of a few formulas to arrive at this measure. You can achieve these complex insights by overlaying the same techniques you would use when doing something relatively simpler.
In the formula for Profit Growth Year On Year, you can see that in evaluating a particular customer, it becomes ranked between a certain value based on a particular metric.
The filtering is done by using RANKX. The customer ranking is based on the Profit This Year versus Profit Last Year which will show the Profit Growth.
Ranking this particular measure will help you know which of these particular customers are within the Top 20 together with the specific results you want to highlight based on this metric.
You can see that the table only shows 20 results when there are, in fact, hundreds of customers. But because the customer data is isolated based on a specific metric, only 20 customers appear in the table.
Jumping to a different time frame will update the results but will still continue to show the Top 20 Customers.
You can also show the Top 20 Customers together with their Profit Growth and show other metrics to try and identify what caused the growth.
This growth in Profit could be due to an increase in Margins or an increase in Sales.
In the formula for Sales Growth Year On Year, you can see that the FILTER function has exactly the same structure as the previous formula, but instead of showing the Profit, it will display the Sales This Year versus Last Year.
This is going to evaluate to TRUE, which in this case is our customers. Then the same logic will play out so that profits between last year and this year stay within in the Top 20 metric.
That’s how you can isolate that particular insight and only show it for the most valued customers.
You can put Sales and Profit Growth in the Dynamic Ranking Table alongside The Top 20 Customers to gain better insights in your reports.
You can also do the exact same thing for Margin Growth.
You can see that the filtering done inside of CALCULATE is exactly the same as how it was done in the previous formulas.
Before, the Top Customers were only identified based on their Profit Growth. But now, the Margin metric can be isolated to see if the reason for the increase in Profit is due to the increase in Margins.
If you look at this table, you can see that the customer (EMD Group) has a negative Margins Growth.
However, you can see that despite having a negative Margins Growth, the customer continues to belong in the Top 20 because of its high Sales Growth resulting in high growths in Profits.
Simply looking at the table can give you more valuable insights into your reports and business.
When you set all this up in Power BI, you’ll see that it’s a great way of drilling into your top and bottom results.
This is a great way of incorporating a number of different measures and visualization techniques to create compelling reports that your consumers are going to love.
All the best,
You're reading Creating Dynamic Ranking Tables Using Rankx In Power Bi
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.
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.
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.
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.
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 2023 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.
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.
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.
Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit more complex subject in comparison to the concept which we discussed in my previous tutorial. You can watch the full video of this tutorial at the bottom of this blog.
In the previous tutorial, we saw how to calculate the percentages in a hierarchical form. We showcased how to calculate ranking over a specific set of data of a particular set of products, either in the form of a product category or subcategory. Also, in the case of the region, it is either in the form of cities, territories, or countries.
This time around, we will go one step further in the calculation of hierarchies. We’ll learn how to calculate ranking when all the different sets of fields are combined in a single set of hierarchies.
Let’s head over to Power BI and see how it is being done. In this scenario, we have created a set of hierarchies where the first level of the hierarchy is in the form of territories.
The second level is in the form of customers.
And the third level is in the form of product names.
So, what’s the logic behind creating this type of hierarchy? We need to find out how we are performing at each and every territorial level. Then, inside each territory, we need to determine who is our best-performing customers.
Additionally, we want to figure out what are the best products our customers like to purchase in their respective territories over a period of time.
To begin with, we will use the wrong approach how to calculate Power BI ranking by using the RANKX function. By doing this, we will understand why the conventional way of doing this will not yield the correct results.
It’s also worth looking at the errors so we can rank the correct measures to evaluate the results for each and every level of the hierarchy in the right manner.
To save time, I copied and pasted the measure based on a simple RANKX function. With this, we created three variables for each of the hierarchies using the simple RANKX function. Additionally, we used the ISINSCOPE function since we are dealing with hierarchies.
Let’s now load this measure and check out the results. As we can see, the territory with the highest total sales is ranked first and the territory with the lowest total sales is ranked last. So far, we are getting the correct results.
Next, check the results for the products to see whether they are also correctly arranged or not.
However, we observed that the customers and products which don’t contain any sales value are still getting ranked. We can stop evaluating these by using the ISBLANK function.
As shown below, we can still see that the first territory and customers for that particular territory are still evaluated correctly.
Scroll down to see how our next territory is ranked.
At this stage, we can now see that there is a problem because the Queensland territory was ranked as second earlier but now, it is evaluated as third.
To be sure, let’s also check how our products are evaluated.
Similarly, the New South Wales territory and the State Ltd customer were both ranked first in the earlier scenarios, but are now second.
Therefore, we cannot use the conventional method of calculating ranking in the case of hierarchies because drill down actually means that we move down from the first level to the next level.
Let’s name our measure as Ranking – Right Measure and create three variables for calculating the ranking for the respective hierarchies.
Type in VAR_Territory_Ranking for our first variable. Inside this variable, the first process is to create a virtual table using SUMMARIZE (Regions, Regions[Territory]). We referenced this to the regions table since territories are coming from that table.
Once we have created our virtual table, inside this, we need to create a virtual column using the ADDCOLUMNS function. This will help us to evaluate the ranking for that particular territory hierarchy.
Then, let’s name our virtual column Territory Ranking and use the RANKX function. We want to rank our highest sales first and the lowest sales last. So, we’ll convert this virtual table into a scalar value by wrapping it up inside the SUMX function.
We implemented the exact same technique for the other two variables such as the VAR_Customers_Ranking and the VAR_Products_Ranking.
Of course, we used the ISINSCOPE function and called out each of our variables against that particular level of hierarchy.
So far, what we’ve done is to create variables for each levels of the hierarchy, and placed each variable against that particular field using the ISINSCOPE function.
It’s time to test our formula and check the results by bringing the measure into our matrix visual and expanding it.
As shown below, the ranking at a territory level is getting evaluated correctly.
Let’s drill down further and check our customer ranking. The New South Wales territory and State LTD customers are ranked correctly as well.
This time around, let’s see how our Queensland territory is evaluated. Finally, it is now ranked accordingly.
For our product levels, it is nice to see that they are also ranked perfectly.
If we’ll also take a look at our second and third customers, we also got the correct results.
Lastly, let’s get rid of the ranking against the blanks by replacing the reference of the respective dimension table names with the Sales table name.
We do this because the Sales table has a lineage with each of these dimension tables. For example, if sales haven’t taken place for a specific product against a particular customer in that territory, then by default, ranking will not be evaluated against that particular data point.
So, let’s go ahead and replace our reference of dimension tables with the Sales Table.
Finally, let’s check the results one last time.
It worked! We can now see that our ranking is only limited to a data point where we have a sales value.
Calculating ranking in a hierarchical form involves complexity especially if you’re dealing with different sets of fields combined in a single set of hierarchies, but it’s worth it because it can really drill down to the next level.
Another important thing to note is that if you would like to see calculate Power BI ranking change based on the selection made into the slicer, then instead of using the ALL function, you can make use of the ALLSELECTED function while evaluating the rankings.
So that’s it for me, I hope you enjoyed this tutorial. There are plenty of resources and further learnings that you can access easily on the Enterprise DNA website, so please check it out.
All the best,
I’m going to show you my most favorite visualization idea around tooltip in Power BI. I saw it demonstrated really well in a Power BI challenge submission and I just want to show you how far you can take it. It’s not that difficult at all and it can add so much value to your report. You may watch the full video of this tutorial at the bottom of this blog.
This visualization technique using the Power BI tooltip will keep your consumers engaged and it enables you to compact more information into a singular report page. You don’t need to build comprehensive reports with ten pages or more. I’m going to show you how you can do that with this great tooltip idea.
Historically, this is what a tooltip has looked like, right? But what if you could customize this tooltip and create a visual tooltip? Wouldn’t that be awesome?
To do this, I first go to the paint roller, to the page size, and then I select tooltip.
This gives me this unique page that is optimized for tooltips. I’m going to grab a table visualization and put some information in it. I want to be able to hover over a customer on my report page, which will then show me some information about the customer.
In my table visual, I put my Date table as well as my Revenue and Orders. I then adjust the table accordingly to fit the page.
Next, I put a simple pie chart below the table. And then I’m going to grab the Channel from my Sales table because I want to see the channel in which a lot of these sales are happening. And I’ll also bring in my Revenue. Then, I just simplify this table and change the data labels into Category, percentage total.
I’m just going to rename this page as Details. You can rename it whatever you’d like. And, because this is a tooltip, I’m going to just hide it because we won’t need it in the application itself.
Lastly, I turn on the Tooltip in the Page information to make this work in the report page.
And now, when I hover over the visualization, I have this dynamic visual tooltip. I just love this idea. It’s so cool!
You can do a lot of other interesting things in the tooltip page that you’ve created. You can play around with the colors of the background, text or fonts, etc. It doesn’t need to be white. Just be creative and think about what could make the visualization pop.
This is just another amazing idea about how you can maximize the Power BI tooltip. I don’t think there’s any other analytical tool that would enable us to do this. It’s pretty epic how a consumer is able to see all this information so quickly and easily.
There’s so much variability in report development now inside Power BI compared to what it was even just a few years ago. Microsoft has done some amazing work!
This is a great visualization feature in Power BI. I recommend that you implement this technique in your own reports. You just need to think strategically about how your model is set up for this to work.
For today’s blog post, I want to talk about a technique that we can use to simulate dynamic theming in a Power BI report. We’ve recently had a few posts on the Enterprise DNA support forum asking how to change the theme of a Power BI report dynamically. In one case, the user sought to have a single content page reflect the theme of multiple departments or categories. You can watch the full video of this tutorial at the bottom of this blog.
As of May 2023, only a single theme JSON file can be used in a Power BI report and a page can have only one background. This is why another technique is needed to accomplish the stated goal.
Here’s what I’ve come up with so far. This is a homepage that has four buttons on it.
There’s one for each category of shipment. If you hover over each category, you can see that the hover behavior is changing its transparency.
You can see that there are only two pages involved in this report. To build the solution, I started with a blank PBIX, then I added a small shipping data set based on the one we used in the recent Enterprise DNA challenge #12.
Then I searched online and found some images that represented the different shipping modes or categories. Then I followed it up by generating a basic color theme using the color theme generator in the Enterprise DNA Analyst Hub.
I chose medium dark blue as a starting point, then used the colors fan function to generate a six-color theme, which I then used to customize the theme of this Power BI report.
Finally, I developed a few DAX measures to return the text and background or font color using the themes desired. Let’s take a quick look.
We’ll open up our Key Measures, then go through our Color Measures folder. Theme Color 6 is just a hex code for one of my themes while Theme Color 5 is my medium blue starting color.
This has a value that is determined by the current category and created using a SWITCH TRUE statement.
If we look at the Table Title Font Background Colour measure, it uses exactly the same SWITCH TRUE statement based on category; but this time, it returns a color theme measure.
I have a similar measure for the color of the font, called Table Title Font Colour, which returns a wider black that contrasts well with the theme color.
And lastly, I have a Table Background Colour measure, which is a duplicate of my Table Title Font Background Colour measure but with a different name to make it easier to set up.
We also have a small table down at the bottom so we can see what the results of those measures are.
That’s it for the setup. Now let’s build the solution together and start with a Home page so that we’ve got something to navigate to.
We’ll create a Content 2 page and add an image for Home.
Bring the image into a corner, make it a bit smaller, and set its action to navigate to the home page.
Then we’ll add a slicer for categories and make the slicer single select.
The next step is to add a text box in the top left corner for the title. Let’s call it ABC Transportation and add a measure to our category.
The title changes when we make different selections on our slicer. We can also change the font color and size.
The next thing to do is add an image for each category. Let’s add the first image for air shipping. We’ll set it to size 1280 and 720 for full size. We’re going to name this in the selection pane.
We’ll do the same steps for the additional images and then add a table. Let’s add a new table to this table. We’ll put it in the category for us to be able to see from our shipments table, as well as the origin country and destination country. From our key measures category, we’re also going to add in the measures we’ve already calculated.
The next thing to do is to format the table. We will set the title text first to the measure that we have already calculated.
We can also change the table font color, table background, and transparency.
That’s it for the content page. Let’s move on to the homepage. To start, we’ll add a blank button here.
We’re going to turn off the outline and size it to 400 and 250.
We’ll set the image fit to fit and the transparency to 0% for On hover so we can see that the button becomes transparent when not selected.
The next step is to set the title. We can make it into a transparent color so we actually can’t see it.
We’ll do this for three more times by copying and pasting the image. Then we’ll adjust these to represent each category.
For the second button, we’re going to change the fill from air shipping to sea shipping.
Then change the image fit to fit and change the name. We’ll then change this background color to a different theme.
For the third button, we’ll change the fill from air shipping to road shipping.
Again, we’ll change the Image Fit to fit and change the title background color.
And for our final button, we will change the fill from air shipping to rail shipping.
And again, we need to set the image fit to fit and change the color of the background to a different theme.
As of May 2023, the current release of Power BI desktop allows the following properties to be set for the table:
The remaining properties cannot be programmatically set at this time. But if needed, one can leverage a solution using multiple table visuals, one for each category and then adjust the product properties and visibility individually as desired.
This can be a bit more complicated, but additional flexibility is possible. Experience shows that additional properties will become customizable in future releases of the Power BI desktop.
Hopefully, the Power BI team will add the ability to use multiple themes in a report. But for now, here’s one technique that allows you to simulate different themes.
Thanks for watching and I wish you all the best on your Power BI journey.
What I want to demonstrate in this tutorial is how we can calculate average sales, profits, or transactions per certain dimension inside of DAX in Power BI. You may watch the full video of this tutorial at the bottom of this blog.
In this particular example we’re going to look at it from a customer’s perspective. We’re going to try and analyze what the average sales are we make per transaction per customer?
This is going to enable us to understand who our best customers are, but also who are our customers that come in and buy a substantial amount.
From here we can ultimately understand what the margins are we’re extracting per transaction from our customers. Are they good in some regions compared to other regions? Are they good for some products compared to other products?
We are going to look at the average amount of products purchased per transaction. Then, I’m also going to show you how to derive even more so you can find even more interesting insights based on this initial one. We’re going to branch out into other things and I’m going to show you how to do it efficiently.
First, we will work out a value per transaction by jumping into the Sales table. We have an order ID column on the left side.
So every order ID equates to every transaction in this particular table. We need to find a way to evaluate every single one of these transactions and essentially average up the sales that we have made for every single transaction.
This is going to give us – depending on the context – the average per transaction. This could be from a regional perspective, a customer perspective, or a sales person perspective.
Some data tables have an Order ID, and then within that Order ID, you might have a number of different transactions. Depending on what average calculation you want to do you’ll probably want to input that column into the calculations. First, let’s calculate average sales.
Let’s create a measure and call this one Average Sales per Transaction. I’m going to use the AVERAGEX function because this will allow us to do these averages by iterating through something. Within AVERAGEX, I’m going to use VALUES and put in my Order ID. Then, I want to average up the Total Sales for every single order.
Once I drag this measure with my Customer Name context, this is the table I come up with:
This will show us on average how much each person makes per transaction every time they come into a store.
This is already a pretty good insight by itself, but we can make this look better using conditional formatting and data bars.
We don’t have to stop here; we can we can go even further. We have some other core calculations like Total Profits and Total Costs. With these calculations, I can find out the average profits per transaction. All I have to do is copy and paste the measure I just used into a new measure, and instead of Total Sales, I’m going to put in Total Profits.
With this new measure, we can work out what our profits are out of every single transaction and then average those up.
I can just drag the new measure into my table to come up with new insights. For example, our customer Chris Fuller has greater profitability per transaction then Philip Foster, who actually made greater sales. This is a pretty good insight, right?
We can also deal with average margins per transaction. We won’t be needing to reference anything from the table because we can actually just use measures within measures.
All we have to do is divide Average Profits per Transaction by Average Sales per Transaction, then input 0 as an alternative result. We also have to make sure that it’s formatted correctly.
Once I drag this into the table, you’ll see now why we have higher profits for Chris Fuller than for Philip Foster’s. Chris has higher margins compared to Philip.
This is a very interesting insight for this particular customer, as well as the rest of our customers.
What’s cool is that we can use this technique on any context. Currently, we’re just using a filter from our Customers table. If you think about it, we can use filters from any of these tables in our data model and see how things change.
We can also take a look at our Average Margins per Transaction over time. I’ll just quickly whip this up using Month & Year and Average Margins per Transaction as values, then put them out into a graph.
We can see how the average margins change through time and see the seasonality.
Overall, it is a lot easier now to see our high margin versus low margin customers. For instance, customer Juan Collins has a 40% Margin. This one sticks out clear as day.
We can also save filter to see which are the most profitable customers per transaction; this could possibly indicate the salesperson assigned to these parts is very good.
We can look further into the insight using DAX in Power BI and determine our best customers on a regional basis. Is there something happening regionally? We can switch from a map visual to a filled map.
We can drill in and have a more in-depth look. In my example, New Hampshire has low margins while every other region is pretty evenly distributed.
You can also select customers from the table, and determine from this a subset of customers where the breakdown was.
We can extract so many great insights when we calculate averages using DAX in Power BI. They also add a lot of value to what we might do within a business from a marketing perspective and allocating sales resources perspective.
You obviously want to be focusing on the clients who purchase the most at the highest margins. And through this type of analysis, you can align your resources to where you feel you’re going to optimize the best results.
In this tutorial, we worked on one thing and then branched out into lots of other things. You can do many calculations and techniques using DAX in Power BI, and find some really good insights.
This type of analytical work is so powerful. If you want to review more examples just like it, check out the Business Analytics Series module at Enterprise DNA Online. This module contains contents around solving real-world business problems using the best practices of DAX in Power BI.
All the best,
Update the detailed information about Creating Dynamic Ranking Tables Using Rankx In Power Bi on the Hatcungthantuong.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!