Top & Bottom 3 in Power BI
Filter top and bottom results in one visual in Power BI
Inspired by a data challenge, I thought about how one could visualize Top and Bottom 3 results in the same visual.
I chose one visualization instead of two because the visualization is easy to read. You directly see which categories are at the top and which are at the bottom. If you can answer a question with one visual instead of two, this also gives you more room to add other essential information on the dashboard.
In this article, I describe the steps to create the visual.
How to create a Top & Bottom 3 in Power BI?
There is no easy way to select Top and Bottom results in the same visual in Power BI. Therefore, I applied a workaround.
Create a “helper” table and put essential information in there. The information I added was the “name” and “quantity” of what I wanted to visualize.
Rank the data in the table. A rank shows you quickly what the position of specific columns is. Therefore, you can easily spot the top and bottom information.
Create a custom column in Dax (see below for the DAX) that provides a True/False statement when a certain condition is met.
DAX & Example to create a top & bottom filter in Power BI
As described earlier you need to create a custom column using DAX to show the Top & Bottom 3 in Power BI. To do so I advise working with variables (“var” in the code), this way you can create variables for the top 3, one for the bottom 3, and finally return the result.
Top Bottom Pizzas =
VAR RankedBottom =
RANKX(
TopBottom,
TopBottom[Sum],,
ASC,
Dense
)
VAR RankedTop=
RANKX(
TopBottom,
TopBottom[Sum],,
Desc,
Dense
)
RETURN
RankedBottom >= 1 && RankedBottom <=5 || RankedTop >= 1 && RankedTop <=5
In the screenshot provided you can see how the DAX transforms the custom column to a boolean value (true, false). The Top 3 and Bottom 3 show “True”, all others in between show “False”.
As the last step, I put a filter on the visual-only selecting values that have the value “True”.