How to create a Dynamic Top N in Power BI: step by step

João Barros 12 de August de 2026 3 min read

This tutorial shows how to create a dynamic Top N in Power BI controlled by a slicer, useful to highlight top customers, products or categories and aggregate the remainder into 'Others'. It explains the why and gives practical steps with DAX and simple modeling.

Prerequisites

Power BI Desktop (recent version).
A sales table with columns: CustomerName, Product, SalesAmount, OrderDate.
Basic knowledge of relationships, tables and DAX measures.

Step 1: Prepare the ranking table

We need a table to select N (Top 3, Top 10, etc.). Create a parameter table in Power BI with numbers that make sense for your report.

TopN = GENERATESERIES(1, 20, 1)

Then convert it to a slicer on the report for the user to choose N. This table is disconnected (doesn't need relationships) and is used for filtering via DAX measures.

Step 2: Total sales measure

Create a base measure for total sales. This serves as the reference for ranking and filters.

Total Sales = SUM(Sales[SalesAmount])

Step 3: Ranking measure by dimension

We will create a measure that calculates the rank of each member (e.g., CustomerName) ordered by Total Sales. We use RANKX with ALLSELECTED to respect relevant slicers.

Customer Rank =
RANKX(
  ALLSELECTED(Customer[CustomerName]),
  CALCULATE([Total Sales]),
  ,
  DESC,
  DENSE
)

Step 4: Measure that identifies Top N

With the rank and the selected TopN value, we create a boolean measure that indicates whether a customer is in the chosen Top N.

Is TopN =
VAR SelectedN = SELECTEDVALUE(TopN[Value], 5)
RETURN
IF([Customer Rank] <= SelectedN, 1, 0)

Step 5: Measure to group 'Others'

We want to show the Top N individually and sum the rest into 'Others'. Create a measure that returns the customer's value if they are in the Top N or aggregates for 'Others'.

TopN Sales =
VAR IsTop = [Is TopN]
RETURN
IF(
  IsTop = 1,
  [Total Sales],
  BLANK()
)

Others Sales =
VAR SelectedN = SELECTEDVALUE(TopN[Value], 5)
VAR TableAll =
    ADDCOLUMNS(
        VALUES(Customer[CustomerName]),
        "Rank", [Customer Rank],
        "Sales", [Total Sales]
    )
VAR OthersSum =
    SUMX(
      FILTER(TableAll, [Rank] > SelectedN),
      [Sales]
    )
RETURN
IF(HASONEVALUE(Customer[CustomerName]) = FALSE(), OthersSum, BLANK())

Step 6: Build the visual with 'Others'

Use a table or column chart. Place Customer[CustomerName] and the TopN Sales measure. To include 'Others' in the same visual, create a virtual helper table with the Top N and an 'Others' row or use a combined visual:

// Simplified example for a virtual table in a table visual
Display Name =
IF([Is TopN] = 1, SELECTEDVALUE(Customer[CustomerName]), "Others")

Display Sales =
IF([Is TopN] = 1, [Total Sales], CALCULATE([Others Sales]))

If you prefer, create a summarized table with SUMMARIZE and use it in a visual with the 'Display Name' and 'Display Sales' fields.

Step 7: Handle ties and performance

Ties in the ranking can duplicate positions; using DENSE avoids gaps, but you can use SKIP for different behavior. For performance, limit ALLSELECTED to a concrete column and avoid unnecessary iterators on large volumes.

Validate the result

Select different values in the TopN slicer and verify that the visual updates: the top N appear individually and the total of the remaining entities appears as 'Others'. Confirm that totals match the overall Total Sales and test with different filters (date, product) to ensure ALLSELECTED respects the intended slicers.

Conclusion

The dynamic Top N in Power BI lets you focus on the main items while keeping context with the aggregated remainder. Next steps: experiment with Top N by product, combine with time variables and create bookmarks for predefined options. Tip: use tooltips to explain to the user what 'Others' represents and avoid confusion.