rank and denseRank number the rows of a visual by a measure: 1 for the best customer, 2 for the next.
They differ only in what happens after a tie. Add a partition and the ranking restarts in each group,
which is how you get the top three products per region.
Copy and paste
Rank by sales, highest first:
rank([sum({Sales}) DESC])
Without gaps after ties:
denseRank([sum({Sales}) DESC])
Rank within each region (Region in the visual):
rank([sum({Sales}) DESC], [{Region}])
Ties broken by name:
rank([sum({Sales}) DESC, {Product} ASC])
Order number of each order within its customer, before aggregation:
rank([{Order Date} ASC], [{Customer}], PRE_AGG)
rank or denseRank
| Product | Sales | rank | denseRank |
|---|---|---|---|
| A | 500 | 1 | 1 |
| B | 300 | 2 | 2 |
| C | 300 | 2 | 2 |
| D | 100 | 4 | 3 |
rank leaves a gap after a tie, like a race: two second places, then fourth. denseRank continues with
the next number. For “top 3” the difference is real: with rank <= 3 you get A, B and C; with
denseRank <= 3 you also get D, because it is the third distinct value.
Top N per group
Create the ranked field with a partition, add a filter on it and keep values less than or equal to 3. The filter applies after the ranking is computed, so each region keeps its own top three. A plain Top and bottom filter on the product field would instead keep the top three overall.
Ranking rows before aggregation
By default rank sorts aggregated values, after the visual has grouped the data. With PRE_AGG it
ranks dataset rows, with an unaggregated field in the sort. The last formula numbers each customer’s
orders by date: 1 for the first order, 2 for the second. A filter on it equal to 1 keeps first orders
only, which is a quick way to analyse new-customer purchases.
PRE_AGG vs PRE_FILTER explained covers the
calculation levels.
Percentile rank
percentileRank uses the same arguments and returns the position as a percentile, which is easier to
compare between groups of different sizes than a plain rank.
Checked against the Amazon Quick Sight function reference (October 2026). The example results were recomputed in DuckDB.
