Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Do Top 5 in Power BI

Cody Schneider••7 min read

Showing your top-performing products, campaigns, or sales reps is one of the most common and valuable things you can do in a report. Instead of showing a long, cluttered list, you can immediately draw attention to what matters most. In this tutorial, we will walk through exactly how to create a "Top 5" view in Power BI using its built-in filter functionality, as well as a more advanced technique for grouping the rest of your data.

GraphedGraphed

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.

Watch Graphed demo video

Why Focus on the Top 5?

Before jumping into the "how," let's quickly discuss the "why." Focusing on a "Top N" (where 'N' is a number like 3, 5, or 10) helps you and your stakeholders instantly see what's driving results. This approach cleans up your dashboards and directs everyone's focus toward the highest-impact areas of the business.

For example, you might want to see:

  • Your top 5 best-selling products by revenue.
  • Your top 5 best-performing ad campaigns by return on ad spend (ROAS).
  • Your top 5 landing pages by user sessions from Google Analytics.
  • Your top 5 sales reps by closed-won deals from Salesforce.

This simple filtering technique transforms a noisy visualization into clear, actionable insights. It’s the difference between showing a list of 100 products and highlighting the 5 that generate 80% of your income.

Method 1: Using the "Top N" Filter (The Easy Way)

The most straightforward method for finding your top performers is using Power BI’s built-in "Top N" filter. This approach is perfect for beginners and requires no special formulas or code. Let’s build a report that shows the top 5 product categories by total sales.

For this example, imagine you have a simple dataset with columns like Product Category and Sales Amount.

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.

Step 1: Create Your Initial Visualization

First, you need a chart to apply the filter to. A clustered bar chart or column chart is a great choice for displaying ranked data.

  1. On the Power BI report canvas, select the Clustered column chart from the Visualizations pane.
  2. Drag the chart onto your canvas and resize it as you see fit.

Step 2: Add Your Data Fields

With your new chart selected, you need to tell Power BI what data to display.

  • Find your data fields in the Data pane on the right-hand side.
  • Drag your descriptive field (e.g., Product Category) into the X-axis field well.
  • Drag your numeric value field (e.g., Sales Amount) into the Y-axis field well.

At this point, you should see a column chart displaying the sales amounts for all your product categories. Now, let’s filter it down to just the top 5.

Step 3: Open the Filters Pane and Apply the Filter

The magic happens in the Filters pane. If it's not visible, go to the View tab in the Power BI ribbon and check the box for "Filters."

  1. With your chart selected, look at the Filters pane. You will see sections for "Filters on this visual," "Filters on this page," and "Filters on all pages." We want to use the one for our specific visual.
  2. You should see the fields you added to your chart listed here (e.g., Product Category). Click the small dropdown arrow next to Product Category to expand its filter options.
  3. Under Filter type, click the dropdown menu that currently says "Basic filtering."
  4. Select Top N from the list.

Step 4: Configure the Top N Filter

Once you select "Top N," new options will appear. This is where you tell Power BI exactly what to show.

  • Show items: Keep this on Top.
  • Input field: Type the number 5 into the box.
  • By value: This is the most important part. You need to tell Power BI how to determine the rankings. Drag your numeric value field (e.g., Sales Amount) from the Data pane into the "By value" box.

Your filter configuration should now look something like this: "Show items: Top 5 by Sales Amount."

Finally, click the Apply filter button.

Your column chart will transform, displaying only the five product categories with the highest sales. That's it! You've successfully created a Top 5 report.

GraphedGraphed

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.

Watch Graphed demo video

Method 2: Showing "Top 5" Plus an "Other" Category (Advanced)

The standard "Top N" filter is great, but it has one limitation: it completely hides everything outside your top group. What if you want to compare your top 5 against the combined total of everything else? To do this, you'll need to use a bit of DAX (Data Analysis Expressions), Power BI's formula language.

Don’t worry, we’ll break it down step-by-step. The goal is to create a new grouping column that categorizes your data into either its own name (if it's in the top 5) or a new catch-all category called "Other."

Step 1: Create a New Calculated Column with DAX

We need to add a new column to our data table that will contain our new groupings.

  1. Go to the Data view in Power BI (the small table icon on the left-hand navigation pane).
  2. Select the table that contains your data (e.g., your 'Sales' table).
  3. From the Table tools ribbon at the top, click New column.
  4. A formula bar will appear. Enter the following DAX formula and press Enter. Be sure to replace Sales[Sales Amount] and Sales[Product Category] with your own table and column names.

Product Sales Grouping = VAR SalesRank = RANKX( ALL('Sales'[Product Category]), CALCULATE(SUM('Sales'[Sales Amount])), , DESC ) RETURN IF( SalesRank <= 5, 'Sales'[Product Category], "Other" )

Step 2: Understanding the DAX Formula

This DAX code might look intimidating, but it's doing something quite logical.

  • VAR SalesRank = RANKX(...): This first part creates a temporary variable named SalesRank. The RANKX function goes through all of your product categories (ALL('Sales'[Product Category])) and ranks them from highest to lowest (DESC) based on their total sales (CALCULATE(SUM('Sales'[Sales Amount]))).
  • RETURN IF(SalesRank <= 5, ...): This second part looks at the rank for each row. If the rank is less than or equal to 5, it returns the actual Product Category name.
  • "Other": If the rank is greater than 5, it assigns the row to a new category simply called "Other."

Step 3: Use the New Column in Your Visualization

Now, head back to the Report view and create a new chart (or modify your existing one).

  1. Select a chart, like a pie chart or a column chart.
  2. Instead of using your original Product Category column on the axis, use your newly created Product Sales Grouping column.
  3. Use your Sales Amount column for the values.

Your visualization will now show separate columns for each of your top 5 categories and a single, combined column for "Other," representing the sum of all remaining categories. This gives you a much better perspective on how much your top performers contribute to the overall total.

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.

A Quick Note on Handling Ties

What if your 5th and 6th product categories have the exact same sales figure? How does Power BI’s "Top N" filter handle a tie? By default, if there is a tie for the last place (the 'Nth' position), Power BI will include all items that have that tied value. So, if you're filtering for a Top 5 and you have two products tied for 5th place, your chart will actually show six products. Keep this in mind when you present your reports, as it can sometimes cause minor confusion.

Final Thoughts

Filtering your reports to show a "Top 5" is a fundamental skill that dramatically improves the clarity and impact of your dashboards. Using the built-in "Top N" filter is quick and easy for most use cases, while a short DAX formula gives you the power to group the remaining items into an "Other" category for a more complete analysis.

While mastering tools like Power BI is a valuable skill, the process of navigating menus, applying filters, and writing formulas can slow you down when you just need a quick answer. We built Graphed to remove this friction by connecting directly to your data sources and allowing you to create entire dashboards simply by describing what you want in plain English. Instead of clicking through menus to make a Top 5 report, you can just ask, "Show me my top 5 products by sales last quarter as a bar chart," and get an interactive visualization back in seconds.

Related Articles