Power BI technique · built-in features only

How to show the top N categories plus 'Others' in Power BI

By · Founder & Reliability Engineer · Updated

The built-in Top N filter drops everything outside the top N. To keep the remainder as one 'Others' bar, build an axis table with every category plus an 'Others' row, and a measure that returns each top-N category's value and, for 'Others', the total of the categories outside the top N.

When to use it. Use it when the long tail matters to the total (share of spend, share of sales) and a chart with every category is unreadable.

Worked example

Worked example · Fieldhouse Sports · spend by category, top 5 + Others

Top N filter (5)

  • Footwear$412K
  • Team kit$288K
  • Fitness$251K
  • Outdoor$197K
  • Racket sports$134K

Bars add up to $1.28M of $1.69M

Sales Top N + Others

  • Footwear$412K
  • Team kit$288K
  • Fitness$251K
  • Outdoor$197K
  • Racket sports$134K
  • Others$411K

    6 categories

Bars add up to $1.69M

Left: the Filters pane's Top N filter hides six categories. Right: the axis table + measure keep them as one Others bar, so the bars still add up to the total.

Fieldhouse Sports is a fictional sports retailer with eleven categories and $1.69M of spend. With N = 5 the top five run from Footwear down to Racket sports. The left panel uses the Filters pane's Top N filter: the six smaller categories vanish and the chart quietly stops adding up to the total.

The right panel uses the axis table and the Sales Top N + Others measure. The six categories outside the top five become one Others bar worth $411K, 24% of spend and almost as large as Footwear itself. That is the insight the plain Top N filter hides: the long tail, taken together, is the size of the biggest category.

If a slicer narrows the page to one store, the measure re-ranks within that store, so the top five can change and Others always holds whatever is left.

Steps

  1. 1

    Create the axis table

    Modeling → New table. It lists every category plus one 'Others' row and isn't related to anything.

    Category Axis =
UNION (
    DISTINCT ( Product[Category] ),
    ROW ( "Category", "Others" )
)
  2. 2

    Write the measure

    It ranks categories by the base measure in the current filter context, then returns the value for top-N rows and the remainder for 'Others'.

    Sales Top N + Others =
VAR N = 5
VAR AxisCategory = SELECTEDVALUE ( 'Category Axis'[Category] )
VAR TopCats = TOPN ( N, ALL ( Product[Category] ), [Sales] )
RETURN
    SWITCH (
        TRUE (),
        AxisCategory = "Others",
            CALCULATE ( [Sales], EXCEPT ( ALL ( Product[Category] ), TopCats ) ),
        AxisCategory IN TopCats,
            CALCULATE ( [Sales], Product[Category] = AxisCategory ),
        BLANK ()
    )
  3. 3

    Build the chart

    Put 'Category Axis'[Category] on the axis and the new measure on values. Categories outside the top N return blank and disappear; 'Others' carries their total.

  4. 4

    Sort with Others last

    Add a sort measure (the value, with 'Others' given -1) and sort the axis by it, or keep Others last with a sort-order column in the axis table.

  5. 5

    Make N adjustable

    Replace VAR N = 5 with a numeric range parameter (Modeling → New parameter → Numeric range) so readers choose N with a slider.

How it works

The axis table is disconnected: it has no relationship to the product table, so each of its rows reaches the measure only through SELECTEDVALUE. The measure then decides what that row means. TOPN returns the top N categories by the base measure in the current filter context, EXCEPT removes them from the full list, and CALCULATE sums the base measure over what is left for the Others row.

Rows of the axis table outside the top N return BLANK, and a visual drops categories whose only measure is blank. That is how one table with every category can produce a chart with only N + 1 bars.

Because the ranking is computed in the measure, it responds to every slicer and filter on the page, unlike a calculated column, which would fix the ranking at refresh time.

Variations

  • Show the rank in a tooltip with RANKX over ALL of the category, so readers can see where a category sits even when it is inside Others.
  • Use the same pattern in a donut chart for share of total, where the Others slice keeps the parts adding up to 100%.

Check that it works

Add a card with the base measure and no category, and a card that sums the Top N + Others measure over the axis table. They must show the same total, here $1.69M; if they differ, a category is falling through both branches of the measure (often a blank category in the product table).

Change N with the parameter slicer from three to eight and watch the Others bar shrink as categories move into the top list. Then slice to a single store and confirm the top list changes with it; a ranking that never changes means the measure is ranking over ALL of the table and ignoring the slicer.

Ties at the boundary need a decision. TOPN returns every row that ties at the cut-off, so with two categories tied for fifth place the chart shows six named bars plus Others. Either accept that, or break ties with a second sort expression such as the category name.

Last, check the Others label never collides with a real category called "Others" or "Other" in your data; rename it in the axis table if it does.

When the visual is a table rather than a chart, add a rank column so readers can see where each named row sits, and keep Others visibly separate with a thin top border or a different font weight, because a reader scanning down a table can mistake it for one more category.

Common mistakes

  • The axis table is disconnected; slicers on Product still work because the measure evaluates [Sales] in the current filter context.
  • Totals: the visual total of this measure is blank (no single category); show the grand total with the base measure.
  • Ties at position N can return more than N categories from TOPN.
  • Sorting by the measure puts Others in the middle when it is large; use a sort helper that forces it last.

FAQ

Can I do this without DAX?

Not with Others. The Filters pane's Top N filter shows the top N and hides the rest.

Does it work in a pie or donut chart?

Yes; any chart that takes a category axis and one measure.

Sources

Menu names follow Power BI Desktop as documented on Microsoft Learn; they can move between releases. The companies and figures in the worked example are invented.

Related

Assets and guides that go with this technique.

More Power BI techniques

Make the report.
Then make it yours.

The Design Studio, templates and tools are free with no account (exports carry a small VizForge mark until you sign in). Sign up when you want AI: 5 credits on us, no card.

Generate a custom visualDesign a page free

Generated visuals are type-checked and built before you download them; one that fails the quality SLA refunds its credits. See all features or pricing.