When Nothing Really Matters

You built a ranked table. Two dimensions, one measure. You coloured the cells by rank so the best performer in each row stands out. You check it. The numbers look right. The colours look wrong.

One column’s top performer is dark blue. The next column’s top performer is a shade lighter. They should be identical. They’re not.

Sound familiar? Here’s what’s happening and how to fix it in one calculated field.

What You’re Building

A cross-tabulation with two dimensions (in this demo: Region across columns, Sub-Category down rows) and one measure (# Customers). Some cells are null. Not every Sub-Category has customers in every Region.

You want to colour each cell by its rank within its column, consistently, so you can visually compare performance across regions at a glance.

The technical challenge: RANK_UNIQUE behaves differently when nulls are present, and neither ascending nor descending ranking solves it on its own.

What you’ll create: A Rank - Colour calculated field that normalises the rank scale across all columns, making colour consistent regardless of how many nulls each column contains.

Why It Breaks

Step 1: The ascending rank problem

RANK_UNIQUE(SUM([# Customers]), 'asc') ranks values within each column from 1 (smallest) upward. The maximum rank in any column equals the number of non-null values in that column.

If Region A has five Sub-Categories with values, its max rank is 5. If Region B has four (one is null), its max rank is 4. Same analytical position at the top of each column. Different rank number. Different colour. You can’t visually compare them.

Step 2: Why descending doesn’t fix it

RANK_UNIQUE(SUM([# Customers]), 'desc') puts the top performer at rank 1 in every column. That part is consistent. But then you flip the colour legend, and nulls (which Tableau assigns a rank of 0) end up at the same end of the colour scale as your rank 1 cells. Your nulls look like top performers. That’s worse.

How to Build This

Workbook

Search When Nothing Really Matters Ranking Nulls VizDJ on Tableau Public. The workbook includes the broken state on two sheets and the fixed state on a third.

Rank – Colour (calculated field)

IF [Rank Asc] > 0
THEN WINDOW_SUM(COUNTD([Region])) - WINDOW_MAX([Rank Asc]) + [Rank Asc]
ELSE 0
END

What this does:

• [Rank Asc] > 0 — catches nulls (which return 0) and sends them to ELSE, assigning colour value 0
• WINDOW_SUM(COUNTD([Region])) — total dimension members = the ceiling
• WINDOW_MAX([Rank Asc]) — actual max rank in this column
• Subtraction shifts each rank up by the null gap, so every top performer lands on the same colour value

Steps to build it

1. Open the workbook from Tableau Public
2. Create Rank - Colour with the formula above. Compute along Region
3. Replace Rank Asc on the Colour shelf with Rank - Colour
4. Verify: top-ranked Sub-Category in each Region column should now share identical colour

Applying to your own data

Replace [Region] with your column dimension, [Sub-Category] with your row dimension, [# Customers] with your measure. Update table calculation scope to match. Formula doesn’t change.

The Bigger Picture

Nulls in ranked tables aren’t a bug in your data. They’re information. The fix here doesn’t hide the nulls — it gives them a consistent, visually distinct colour at the bottom of the scale so they read as “no data” rather than “worst performer.”

A null and a zero are not the same thing.

Workbook on Tableau Public: search When Nothing Really Matters Ranking Nulls VizDJ