Dynamic Comparison: Visualizing Top and Bottom Performers in One Visual
Unlock the Power of Seamless Comparison between Top and Bottom Performers with this Step-by-Step Guide
Imagine you have a long list of items, and you want to see which ones are at the top and which ones are at the bottom. But you don’t want to switch between different views or use complicated buttons. If this sounds familiar, you’re in the right place.
In this blog post, we’ll show a solution that allows you to visualize both the top and bottom sections of your data simultaneously within a single table visual. Picture this: a seamless display where the top-performing items are shown alongside the underperformers, with an icon (“⋮”) in between. And the best part? It’s all dynamic, giving users the freedom to adjust the number of items they see in each section on the fly.
Our method maintains the same coordinates for both the top and bottom sections, preserving a crucial aspect that facilitates easy comparison between these two parts. While separate views or visuals might be a better option for comparing items within each top or bottom group with themselves.
Join us in this step-by-step guide as we explore this innovative technique and maybe improve your data analysis experience in Power BI. Let’s get started.
Tutorial
Step 1: Create a Disconnected Table for Dynamic Selection
First, let’s set up a disconnected table in Power BI. This table lets users choose how many items they want to see in both the top and bottom sections of the visual. Click on “New Table” and use the GENERATESERIES function to make a range of numbers representing the user’s selection. Alternatively, you can create this table using the “New Parameter” feature. Then, select the column you created as a slicer. This will serve as a dynamic selector.
Step 2: Create a Measure for Ranking
Now, let’s create a measure that will rank the items based on your criteria. Once created, we’ll add this measure along with item IDs or names to the table visual. This measure assigns a rank to each item based on your criteria, so you can see the ranked items in the table visual. You can also add other measures or calculations to provide more insights for items.
Step 3: Dynamic TopN/BottomN Selection with Intermediary Items
Make a dynamic measure to select the top N bottom N items based on the slicer’s selection. This measure uses the TOPN function twice: once in ascending order to find the bottom N items, and once in descending order to find the top N items. We also include one or two rows between these two sets. To do this, we use the RANK measure (step 2) to determine the rank of items one or two positions above the selected N number.
Then, we use the SWITCH function to assign a value of 1 if the items belong to the top N or bottom N categories, or if their rank is one or two positions higher than the selected N number; otherwise, the value is set to 0.
This method selects the top N, bottom N, and one or two intermediary items between them.
Step 4: Adding the Measure to the Filter Pane
Next, add this measure to the filter pane of the table visual and set its value to 1. This ensures that only the top N, bottom N, and intermediary items are dynamically filtered based on the user’s selection of the number of items from the slicer.
Step 5: Customizing Intermediate Row Display
Adjust the rows between the top N and bottom N sections to either display nothing or show the ellipsis icon “⋮”. This icon indicates that there are rows between these top and bottom sections but they are not included in the selection. To do this, create a measure that checks if the ranking is equal to the rank of those intermediate rows, and if so, displays the ellipsis icon.
Additionally, use another measure for color conditional formatting to set the color of those intermediate rows between the top and bottom to white (the color of background or transparent). This approach eliminates the need to create a measure for each field and ensures compatibility with the ellipsis icon for the intermediary rows.
However, this approach is flexible. You have the option to choose whether to add the ellipsis icon “⋮” to one or two fields and make the other fields in the intermediate rows white, thus displaying nothing. Alternatively, you can opt to display the ellipsis icon “⋮” for all fields by creating a measure for each field, showing “⋮” for the intermediate rows.
Step6: Enhancing Visual Clarity with Color Conditional Formatting
Enhance visual clarity by using color conditional formatting to highlight the top and bottom rows, reflecting their performance. Make sure this feature dynamically adjusts based on the user selection, so the code for this resembles what we used for filtering top N, bottom N, and intermediary rows (step 3). However, instead of assigning a value of 1, we assign colors. This is particularly effective when applied to the column used for ranking, making it easier to identify the field that ranks the items.
Scenario and Considerations:
This approach has its benefits: both top and bottom rows share the same coordinates, allowing the bar chart to clearly display differences between these rows. Additionally, it provides a user-friendly and visually appealing view of the top and bottom items. But, there are some considerations to keep in mind. In some cases, when comparing the values of the top with the bottom, it may appear that the bottom part doesn’t contribute much, as its values are significantly lower and may not even be visible for comparison with the top rows. In such cases, it might be more effective to create separate visuals for the top and bottom parts.
But there is also another solution for this. As demonstrated in this scenario, it’s possible to dynamically adjust the visualization by giving users the option to choose the ranking field, the field by which items are ranked. In this scenario, a dashboard designed for analyzing agents’ performance, users can easily select the top N and bottom N agents and adjust the number of agents they want to see. They can also choose different fields to rank the agents, allowing them to evaluate agents performance effectively. This scenario illustrates a use case for this visual and its utility in a business performance dashboard.
Don’t forget to subscribe to
and join our Power BI community
