Does a context filter fix Top N per category?

Only when one category is shown at a time. With several categories in the view, the Top N filter still returns N items in total, not N per category.

Can Tableau collapse the tail into a single Other mark?

Not with a table calculation alone: it labels each title, and each title stays its own mark. Colour by the label and stack the bars, and the tail reads as one block. For a true single Other row, group it in SQL or in your data prep.

Do the hidden titles still count in the category total?

Yes. A table calculation filter runs after totals, so subtotals keep the titles it hides. That's usually what you want: the Other bar and the total both stay honest.

Recipe Tableau and SQL

How do I show the top N in each category, with the rest as Other?

Q: What does QUALIFY do?

It filters on a window function in the same query, the way HAVING filters on an aggregate: QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC) <= 3. DuckDB, Snowflake and BigQuery support it; elsewhere, wrap the query and filter outside.

Rank inside each category, not across the whole table: RANK_UNIQUE(SUM([revenue]), 'desc') restarting every category in Tableau, ROW_NUMBER() OVER (PARTITION BY category ...) in SQL. Then label anything past N as Other. Keep the Other bar: in this bookshop, the top 3 fiction titles make only 12.8% of fiction sales.

Updated October 30, 2026

The data

Larkspur Books, sales in 2025

Synthetic datasales.csv

An independent bookshop with five sections: lots of fiction that sells steadily, one runaway cookbook, picture books with two exact ties.

`sale_date`	`category`	`title`	`copies`	`revenue`
2025-01-01	Children's	Count the Clouds	1	12
2025-01-01	Fiction	Blue Hour	1	14
2025-01-01	Fiction	Stillwater	1	16
2025-01-01	Fiction	The Orchardist's Daughter	1	15

Step 1

Total every title

74 titles, far too many for one chart. One cookbook towers over everything; fiction is a long, flat run of similar bars.

Tableau

SUM([revenue])

SQL

SELECT category, title, SUM(revenue) AS revenue
FROM input
GROUP BY category, title
ORDER BY revenue DESC, title

Rows

Category	Title	Revenue
Cookbooks	One Pan Everything	$5,824
Popular science	The Hidden Life of Fungi	$1,344
Children's	The Very Brave Badger	$1,104
Popular science	Why Clocks Tick	$1,012
Children's	Moon Snail's Big Day	$972
Popular science	Ocean in a Drop	$902
Travel	Walking the Coast Path	$902
Cookbooks	Sourdough at Home	$840
Children's	Pip and the Puddle	$770
Children's	Ten Sleepy Owls	$770
Fiction	Stillwater	$672
Cookbooks	Fermented	$660
Fiction	The Last Cartographer	$630
Fiction	The Tin Crown	$630
Fiction	The Clockmaker's Wife	$615
Fiction	The Orchardist's Daughter	$615
Fiction	The Night Gardener	$608
Fiction	Every Other Summer	$602
Fiction	A House Called Tomorrow	$600
Fiction	North of Nowhere	$592
Fiction	Small Hours	$592
Cookbooks	The Baking Year	$578
Fiction	A Map of Small Lies	$576
Fiction	Blue Hour	$574
Fiction	Under the Linden	$570
Fiction	Harbour Lights	$555
Fiction	Bright Hollow	$544
Fiction	The Weaving Room	$525
Fiction	What the River Kept	$512
Fiction	The Glass Ferry	$510
Fiction	The Lantern Keeper	$510
Cookbooks	Smoke and Fire	$504
Fiction	Paper Moons	$496
Fiction	Winter of the Heron	$495
Fiction	The Salt Orchard	$490
Popular science	How Bridges Stand	$480
Fiction	Folded Letters	$476
Cookbooks	Salt Fat Sunday	$476
Children's	Mabel's Map	$468
Cookbooks	Soup for Every Season	$468
Fiction	Ash and Juniper	$465
Cookbooks	Breakfast All Day	$442
Fiction	The Ninth Wave	$435
Fiction	The Quiet Season	$420
Fiction	The Long Table	$392
Fiction	The Second Mrs. Ames	$392
Cookbooks	The Weeknight Table	$390
Popular science	A Short History of Salt	$380
Cookbooks	Little Plates	$364
Travel	Islands of Scotland	$360

First 50 of 74 rows.

Step 2

Try the Top 10 filter

The obvious move, and it hides the shop's biggest section. Fiction sells the most of any section, yet not one fiction title makes the overall top 10: its best seller ranks 11th. A Top N filter ranks across the whole view before any category splits it.

Tableau

Filter on title: Top 10 by SUM([revenue])

SQL

SELECT category, title, SUM(revenue) AS revenue
FROM input
GROUP BY category, title
ORDER BY revenue DESC, title
LIMIT 10

Rows

Category	Title	Revenue
Cookbooks	One Pan Everything	$5,824
Popular science	The Hidden Life of Fungi	$1,344
Children's	The Very Brave Badger	$1,104
Popular science	Why Clocks Tick	$1,012
Children's	Moon Snail's Big Day	$972
Popular science	Ocean in a Drop	$902
Travel	Walking the Coast Path	$902
Cookbooks	Sourdough at Home	$840
Children's	Pip and the Puddle	$770
Children's	Ten Sleepy Owls	$770

Common mistake

A Top N filter across the whole view

A Top N filter or a plain LIMIT picks the best N titles in the shop, then splits them by section. Sections with many mid-sized sellers vanish. Rank inside each partition and filter on that rank.

WrongStarts with Cookbooks, Popular science, Children's…

Point at the line to read it.

Tableau

Filter on title: Top 3 by SUM([revenue])

SQL

SELECT category, title, SUM(revenue) AS revenue
FROM input
GROUP BY category, title
ORDER BY revenue DESC
LIMIT 15

RightStarts with Children's, Children's, Children's…

Point at the line to read it.

Tableau

RANK_UNIQUE(SUM([revenue]), 'desc') <= [Top N]
// on Filters, True; Compute Using: title

SQL

SELECT category, title, SUM(revenue) AS revenue
FROM input
GROUP BY category, title
QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC, title) <= 3
ORDER BY category, revenue DESC

Step 3

Rank inside each section

Now every section has its own number one. The key is Compute Using: along title, with category left out, so the rank restarts at 1 in each section. With Table (down) it would rank across all 74 titles again.

Tableau

RANK_UNIQUE(SUM([revenue]), 'desc')
// Compute Using: title, so it restarts for every category

SQL

SELECT
  category,
  title,
  SUM(revenue) AS revenue,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC, title) AS rank_in_category
FROM input
GROUP BY category, title
ORDER BY category, rank_in_category

Rows

Category	Title	Revenue	Rank in category
Children's	The Very Brave Badger	$1,104	1
Children's	Moon Snail's Big Day	$972	2
Children's	Pip and the Puddle	$770	3
Children's	Ten Sleepy Owls	$770	4
Children's	Mabel's Map	$468	5
Children's	Count the Clouds	$336	6
Children's	The Dragon Who Hated Tea	$272	7
Children's	A Hat for Hedgehog	$261	8
Children's	Rocket Ruth	$250	9
Children's	Busy Diggers	$232	10
Children's	Where Do Whales Sleep?	$207	11
Children's	Zoe and the Zebra Bus	$204	12
Children's	The Muddy Puddle Club	$198	13
Children's	Bedtime on Mars	$180	14
Children's	The Sock Monster	$180	15
Children's	Goodnight, Lighthouse	$168	16
Cookbooks	One Pan Everything	$5,824	1
Cookbooks	Sourdough at Home	$840	2
Cookbooks	Fermented	$660	3
Cookbooks	The Baking Year	$578	4
Cookbooks	Smoke and Fire	$504	5
Cookbooks	Salt Fat Sunday	$476	6
Cookbooks	Soup for Every Season	$468	7
Cookbooks	Breakfast All Day	$442	8
Cookbooks	The Weeknight Table	$390	9
Cookbooks	Little Plates	$364	10
Cookbooks	Vegetable Kingdom Kitchen	$308	11
Cookbooks	Noodle Nights	$270	12
Fiction	Stillwater	$672	1
Fiction	The Last Cartographer	$630	2
Fiction	The Tin Crown	$630	3
Fiction	The Clockmaker's Wife	$615	4
Fiction	The Orchardist's Daughter	$615	5
Fiction	The Night Gardener	$608	6
Fiction	Every Other Summer	$602	7
Fiction	A House Called Tomorrow	$600	8
Fiction	North of Nowhere	$592	9
Fiction	Small Hours	$592	10
Fiction	A Map of Small Lies	$576	11
Fiction	Blue Hour	$574	12
Fiction	Under the Linden	$570	13
Fiction	Harbour Lights	$555	14
Fiction	Bright Hollow	$544	15
Fiction	The Weaving Room	$525	16
Fiction	What the River Kept	$512	17
Fiction	The Glass Ferry	$510	18
Fiction	The Lantern Keeper	$510	19
Fiction	Paper Moons	$496	20
Fiction	Winter of the Heron	$495	21
Fiction	The Salt Orchard	$490	22

First 50 of 74 rows.

Step 4

Label the top N, call the rest Other

Each title now knows which side of the line it's on. Move the slider: the line moves inside every section at once.

Tableau

IF RANK_UNIQUE(SUM([revenue]), 'desc') <= [Top N] THEN ATTR([title])
ELSE "Other"
END
// [Top N] is an integer parameter

SQL

SELECT
  category,
  title,
  SUM(revenue) AS revenue,
  CASE
    WHEN ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC, title) <= 3 THEN 'Top {knob.n}'
    ELSE 'Other'
    END AS bucket
FROM input
GROUP BY category, title
ORDER BY category, revenue DESC, title

Rows

Category	Title	Revenue	Bucket
Cookbooks	One Pan Everything	$5,824	Top 3
Popular science	The Hidden Life of Fungi	$1,344	Top 3
Children's	The Very Brave Badger	$1,104	Top 3
Popular science	Why Clocks Tick	$1,012	Top 3
Children's	Moon Snail's Big Day	$972	Top 3
Popular science	Ocean in a Drop	$902	Top 3
Travel	Walking the Coast Path	$902	Top 3
Cookbooks	Sourdough at Home	$840	Top 3
Children's	Pip and the Puddle	$770	Top 3
Children's	Ten Sleepy Owls	$770	Other
Fiction	Stillwater	$672	Top 3
Cookbooks	Fermented	$660	Top 3
Fiction	The Last Cartographer	$630	Top 3
Fiction	The Tin Crown	$630	Top 3
Fiction	The Clockmaker's Wife	$615	Other
Fiction	The Orchardist's Daughter	$615	Other
Fiction	The Night Gardener	$608	Other
Fiction	Every Other Summer	$602	Other
Fiction	A House Called Tomorrow	$600	Other
Fiction	North of Nowhere	$592	Other
Fiction	Small Hours	$592	Other
Cookbooks	The Baking Year	$578	Other
Fiction	A Map of Small Lies	$576	Other
Fiction	Blue Hour	$574	Other
Fiction	Under the Linden	$570	Other
Fiction	Harbour Lights	$555	Other
Fiction	Bright Hollow	$544	Other
Fiction	The Weaving Room	$525	Other
Fiction	What the River Kept	$512	Other
Fiction	The Glass Ferry	$510	Other
Fiction	The Lantern Keeper	$510	Other
Cookbooks	Smoke and Fire	$504	Other
Fiction	Paper Moons	$496	Other
Fiction	Winter of the Heron	$495	Other
Fiction	The Salt Orchard	$490	Other
Popular science	How Bridges Stand	$480	Other
Cookbooks	Salt Fat Sunday	$476	Other
Fiction	Folded Letters	$476	Other
Children's	Mabel's Map	$468	Other
Cookbooks	Soup for Every Season	$468	Other
Fiction	Ash and Juniper	$465	Other
Cookbooks	Breakfast All Day	$442	Other
Fiction	The Ninth Wave	$435	Other
Fiction	The Quiet Season	$420	Other
Fiction	The Long Table	$392	Other
Fiction	The Second Mrs. Ames	$392	Other
Cookbooks	The Weeknight Table	$390	Other
Popular science	A Short History of Salt	$380	Other
Cookbooks	Little Plates	$364	Other
Popular science	The Body Electric	$360	Other

First 50 of 74 rows.

Top N per section318

Step 5

Fold the tail into one Other bar

One bar per section, the top N stacked on the tail. Sort by the rank, not by revenue, so Other always sits last. Tableau stacks one mark per title in the same colour; SQL folds them into a single row.

Tableau

IF RANK_UNIQUE(SUM([revenue]), 'desc') <= [Top N] THEN "Top N"
ELSE "Other"
END
// on Color; category on Columns, stacked bars

SQL

WITH ranked AS (
  SELECT
    category,
    title,
    SUM(revenue) AS revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC, title) AS r
  FROM input
  GROUP BY category, title
)
SELECT
  category,
  CASE WHEN r <= 3 THEN title ELSE 'Other' END AS label,
  CASE
    WHEN r <= 3 THEN 'Top {knob.n}'
    ELSE 'Other'
    END AS bucket,
  SUM(revenue) AS revenue,
  MIN(r) AS sort_order
FROM ranked
GROUP BY ALL
ORDER BY category, sort_order

Rows

Category	Label	Bucket	Revenue	R
Children's	The Very Brave Badger	Top 3	$1,104	1
Cookbooks	One Pan Everything	Top 3	$5,824	1
Fiction	Stillwater	Top 3	$672	1
Popular science	The Hidden Life of Fungi	Top 3	$1,344	1
Travel	Walking the Coast Path	Top 3	$902	1
Children's	Moon Snail's Big Day	Top 3	$972	2
Cookbooks	Sourdough at Home	Top 3	$840	2
Fiction	The Last Cartographer	Top 3	$630	2
Popular science	Why Clocks Tick	Top 3	$1,012	2
Travel	Islands of Scotland	Top 3	$360	2
Children's	Pip and the Puddle	Top 3	$770	3
Cookbooks	Fermented	Top 3	$660	3
Fiction	The Tin Crown	Top 3	$630	3
Popular science	Ocean in a Drop	Top 3	$902	3
Travel	The Road to Marrakech	Top 3	$336	3
Children's	Other	Other	$3,726	4
Cookbooks	Other	Other	$3,800	4
Fiction	Other	Other	$13,161	4
Popular science	Other	Other	$2,312	4
Travel	Other	Other	$1,190	4

Why keep the Other bar?

Because the tail can be most of the business. At N = 3, Other holds 87.2% of fiction sales and 34.2% of cookbook sales. A "top 3 per section" chart without it says the cookbook section is one book, which is nearly true, and says fiction is three books, which is badly wrong: fiction is a long run of steady sellers, none of them a hit. Drop Other and that difference disappears.

Common mistake

Sorting the Other row by revenue

Other is a sum of many titles, so it is often the biggest row. Here fiction's Other bar is larger than the runaway cookbook. Sort by revenue and Other jumps to the top of the list; sort by the rank and it stays last.

WrongStarts with Children's, Children's, Children's…

Point at the line to read it.

SQL

WITH ranked AS (
  SELECT
    category,
    title,
    SUM(revenue) AS revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC, title) AS r
  FROM input
  GROUP BY category, title
)
SELECT
  category,
  CASE WHEN r <= 3 THEN title ELSE 'Other' END AS label,
  SUM(revenue) AS revenue
FROM ranked
GROUP BY ALL
ORDER BY category, revenue DESC

RightStarts with Children's, Children's, Children's…

Point at the line to read it.

SQL

WITH ranked AS (
  SELECT
    category,
    title,
    SUM(revenue) AS revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC, title) AS r
  FROM input
  GROUP BY category, title
)
SELECT
  category,
  CASE WHEN r <= 3 THEN title ELSE 'Other' END AS label,
  SUM(revenue) AS revenue
FROM ranked
GROUP BY ALL
ORDER BY category, MIN(r)

Step 6

How much does Other hold?

The same N tells a different story per section. Cookbooks are mostly one book; fiction is mostly tail.

Tableau

WINDOW_SUM(IF RANK_UNIQUE(SUM([revenue]), 'desc') > [Top N] THEN SUM([revenue]) END) / WINDOW_SUM(SUM([revenue]))
// category on Columns, title on Detail; Compute Using: title

SQL

WITH ranked AS (
  SELECT
    category,
    title,
    SUM(revenue) AS revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC, title) AS r
  FROM input
  GROUP BY category, title
)
SELECT
  category,
  SUM(revenue) FILTER (WHERE r > 3)
    / SUM(revenue) AS other_share
FROM ranked
GROUP BY category
ORDER BY other_share DESC

Rows

Category	Revenue
Fiction	87.2%
Children's	56.7%
Travel	42.7%
Popular science	41.5%
Cookbooks	34.2%

How do I show the top N in each category, with the rest as Other?

Larkspur Books, sales in 2025

Total every title

Try the Top 10 filter

A Top N filter across the whole view

Rank inside each section

Label the top N, call the rest Other

Fold the tail into one Other bar

Why keep the Other bar?

Sorting the Other row by revenue

What happens with ties?

RANK when two titles tie at the cut-off

Run it on your own data

Questions

Does a context filter fix Top N per category?

What does QUALIFY do?

Can Tableau collapse the tail into a single Other mark?

Do the hidden titles still count in the category total?

Sources