Power BI: Show Only the Top 10 Values in Visualization

Sometimes we only want to see the top 10 (or bottom 10) categories in a chart to get a good sense of the data. This is especially useful when we have too many categories in our dataset.

Let’s say we have the following population data for all states in the US (50 rows). But we only want to see the top 10 states with highest population.

Show only Top 10 in Chart Using Filters

Create a visualization (pie chart, bar chart etc) and follow these steps:

1. For the above data, in the Visualizations pane, add State to Legend and Details. Add Population to the Values section in the visualization (pie chart here)

2. In the Filters pane, under Filters on this visual, select the State dropdown to expand. In your case, choose the category that you want to filter

3. Under Filter type dropdown, select Top N (or Bottom N). Enter the number of categories you want to see on the visualization. And remember to click the Apply filter, otherwise the changes won’t take place.

Here’s the same as above in bar chart. Left side has no filters. Right side has filter of Top 10.

Show only Top 10 in Chart Using DAX

Using TOPN function to get top 10 categories

You can create new table with the top 10 values and then use this new table to create your visualization. This trick may come in handy in some cases.

Here’s the code for our example:

TOPN(<N_Value>, <Table>, <OrderBy_Expression>, [<Order>[, <OrderBy_Expression>, [<Order>]]…])

Top States = TOPN(10, population, population[Population],1)

Here’s the final table for top 10: You can change the code to show top 5, top 100 etc, or any number you’d like.

Here’s Microsoft’s documentation on TOPN function.

Using RANKX to get top 10 categories

You can create a new column in the existing table to rank the category (State in our case) by population (or any other value)

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

rank of state = RANKX(population, population[Population], population[Population])

Here’s Microsoft’s documentation on RANKX function.

Show Top 10 Categories Using Power Query Editor

You can filter the entire table to the top 10 (or top n) using power query and filter out the rest. The rest of the data will not show in Table view outside of power query.

1. Go to Home > Transform Data to open Power Query Editor
2. Under the Home tab, click the Sort Ascending or Sort Descending button depending on your use case.

3. Under the Home tab, click on Reduce Rows dropdown, then Keep Rows > Keep Top Rows and a popup window will appear.

4. Enter the number of rows of data you’d like to keep

5. The resulting table will have as many rows as you entered in the step above. If you made a mistake, you can delete the steps under the Applied Steps pane on the right side.

Show Top 10 Categories and Group Smaller Categories Together

Group Smaller Categories Using Calculated Column

If your table is not very large, you can create a new calculated column to assign new values to the categories. In our case, we will pick the top 10 states by population and mark the rest as “Others”. Then we can use this new column in our visualizations.

TopNStates = 
-- Variable for how many values you want to see as top
VAR keepStates = 10

-- Rank the existing States by population. Highest population will get rank of 1 etc
VAR addRank = RANKX(population, population[Population], population[Population])

-- If rank of state is less than or equal to variable keepStates (10), then keep the state name, otherwise, use the string "Others"
VAR result =
    IF( addRank <= keepStates, population[State], "Others" )
    
RETURN
    result

Group Smaller Categories Using Calculated Table

Similar to calculated column, you can also create a new table. This is especially useful is your main table is too large and calculated column would take too long to process thus slowing down your report.

TopNStates = 
VAR keepStates = 10
VAR tbl = 
    ADDCOLUMNS(
        VALUES( population[State] ),
        "@TotalPop", CALCULATE( sum(population[Population] ))
    )
VAR addRank =
    ADDCOLUMNS(
        tbl,
        "@Rank", RANKX(tbl, [@TotalPop], , DESC)
    )
VAR result =
    SELECTCOLUMNS(
        addRank,
        "StateKey", population[State],
       "TotalPop", CALCULATE( sum(population[Population] )),
        "NewLabel", IF( [@Rank] <= keepStates, population[State], "Others" )
    )
RETURN
    result

This is the result where we have all the State Names, the population and a new column that takes the state name for the top 10 and “Others” for the rest. You can use this new column in your visualizations and sum the populations.

When you create a new visual using the new columns, you get the following result