How to Show Top 5 and Bottom 5 in Power BI

Displaying your best and worst performers in a single Power BI chart gives you a powerful, at-a-glance view of what’s working and what isn’t. Instead of creating separate visuals for your top products and your bottom products, you can combine them for a more focused and impactful report. This tutorial will walk you through exactly how to set up a visual that shows both the top 5 and bottom 5 results together using DAX.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Combine Top and Bottom Performers in One Visual?

Before jumping into the “how,” it’s helpful to understand the “why.” Separating your top and bottom items into different charts isn’t wrong, but it can make your reports feel disconnected. When you place them in the same visual, you create a clear and immediate comparison point.

This approach is perfect for answering questions like:

• Which sales reps are leading the team, and who might need more support?
• Which marketing campaigns are driving the most conversions, and which are draining our budget with little return?
• Which products are flying off the shelves, and which are collecting dust in the warehouse?

By showing this complete performance spectrum, you give stakeholders a focused and actionable snapshot without forcing them to jump between multiple tables or graphs. It's an efficient way to tell a compelling story about your data.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

The Challenge: Standard Filters Won't Work

If you've tried this before, you might have run into a roadblock. The standard "Top N" filter in Power BI's Filters pane is great for showing just the top items. However, you can't apply a "Top N" filter and a "Bottom N" filter to the same visual simultaneously. They are mutually exclusive, so you have to choose one or the other.

The solution is to bypass these built-in filters entirely and create our own custom grouping logic with DAX (Data Analysis Expressions). The strategy is simple: we'll create a new calculated column that "tags" each item in our data as either "Top 5", "Bottom 5", or "Other". Once we have this tag, we can use it to filter our visual to show only the groups we care about.

Step 1: Prepare Your Data Model

For this example, we’ll use a simple dataset of fictional product sales. All you need is a table with at least two columns: one for the category you want to rank (like Product Name) and one for the value you'll use for ranking (like Sales Amount).

It’s a Power BI best practice to work with measures rather than raw column values in your visuals. So, first, make sure you have a basic measure for your value field. If you don't have one already, it's easy to create.

In the Report view, right-click on your table in the 'Data' pane and select "New measure". Then, enter this simple formula:

Total Sales = SUM(Sales[SalesAmount])

Using this measure will make your DAX formulas more flexible and reliable as your reports grow in complexity.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Create a Calculated Column for Ranking

Now, we'll create the calculated column that forms the backbone of this technique. This column will assign a rank to each product based on its total sales and then label it accordingly.

1. Navigate to the Data View (the grid icon on the left-hand side of Power BI).
2. Select the table that contains the data you want to rank (in our case, the Products table).
3. In the "Column tools" tab, click "New column". A formula bar will appear at the top.
4. Here’s the DAX formula you’ll use. Copy and paste this into the formula bar and press Enter.

Top/Bottom Group = VAR TopRank = RANKX(ALL('Products'), [Total Sales], , DESC, Dense) VAR BottomRank = RANKX(ALL('Products'), [Total Sales], , ASC, Dense) RETURN IF( TopRank <= 5, "Top 5", IF( BottomRank <= 5, "Bottom 5", "Other" ))

Breaking Down the DAX Formula

This formula might look complex, but it's doing four straightforward things:

• VAR TopRank: We first create a variable named TopRank. It uses the RANKX function to calculate the rank of each product based on [Total Sales].
• VAR BottomRank: We do the same thing again, but for our bottom performers, setting the order to ASC for "ascending," so the product with the lowest sales gets rank #1.
• RETURN: After defining our two rank variables, we use a simple IF statement.
• The IF Logic:

After you apply the formula, you can scroll through your table in the Data view and see the new Top/Bottom Group column with the correct labels assigned to each product.

Step 3: Building and Filtering Your Visual

With the hard part done, visualizing the data is easy. Let's go back to the Report view and build our chart.

1. Add a chart to the canvas. A bar chart or column chart usually works best for ranking. We'll use a "Clustered bar chart".
2. Configure the chart's fields:

At this point, you'll see a chart showing all of your products. Now it's time to filter.

1. Apply the filter:

Voila! Your chart will instantly update to show only the 10 products you’re interested in.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Sorting and Styling for Clarity

Just one last thing. By default, Power BI might sort your chart alphabetically by product name. That’s not very helpful for a ranking visual. To fix this:

• Click the three dots (...) in the top-right corner of your chart.
• Go to Sort axis > and select [Total Sales].
• Go back and in the same menu, set the sort order to Sort descending to show the highest performers at the top.

For an extra touch, you can use your Top/Bottom Group for color-coding. Drag the Top/Bottom Group column to the Legend field on your visual. Power BI will automatically assign different colors to distinguish your top performers from your bottom performers, making the chart even easier to read.

Final Thoughts

By creating a custom DAX column to categorize performers, you can bypass Power BI's standard filter limitations and build highly focused, insightful visuals. This method for showing top and bottom items together delivers a complete performance summary that's easy for anyone to understand and incredibly useful for strategic decision-making.

Once you are comfortable with this approach, you are on your way to creating stunning reports that go beyond defaults. We built Graphed because we believe achieving these kinds of insights should be even simpler. Instead of writing DAX, you can connect your data and just ask a question like, "Show me my top 5 and bottom 5 products by sales, color-coded," and get a live dashboard instantly. It lets you focus on your business strategy, not the hours spent manually creating reports.