![](https://www.thedataschool.co.uk/content/images/2022/05/Picture1-1.png)
Appending appropriate suffixes to numeric ranks (e.g., 1, 2, 3 → 1st, 2nd, 3rd) is a problem I've repeatedly encountered in Tableau. What's more, I haven't spotted a definitive online solution. Here, I'll describe one approach comprising a few simple steps.
(1) Rank your data
//[Rank]
RANK(SUM([Value]))
// This calculation ([Rank]) can assign ranks to a dimension based on the sum of [Value]
(2) Cast the following incantation to return ordinal ranks as strings
//[Rank + Suffix]
IF ENDSWITH(STR([Rank]), '11') THEN STR([Rank])+'th'
ELSEIF ENDSWITH(STR([Rank]), '12') THEN STR([Rank])+'th'
ELSEIF ENDSWITH(STR([Rank]), '13') THEN STR([Rank])+'th'
ELSEIF ENDSWITH(STR([Rank]), '1') THEN STR([Rank])+'st'
ELSEIF ENDSWITH(STR([Rank]), '2') THEN STR([Rank])+'nd'
ELSEIF ENDSWITH(STR([Rank]), '3') THEN STR([Rank])+'rd'
ELSE STR([Rank])+'th'
END
(3) Expand [Rank + Suffix] depending on specific requirements (opt.)
We can modify the [Rank + Suffix] calc. to account for cases where a row's rank is absent e.g., NULL or special character.
//[Rank + Suffix]
IF ENDSWITH(STR([Rank]), '11') THEN STR([Rank])+'th'
. . .
. . .
ELSEIF ENDSWITH(STR([Rank]), '–') THEN (STR([Rank]) // or ''
ELSE STR([Rank])+'th'
END
We can also prevent ranks from appearing until parameter-based filtering has occurred. In other words, aggregated data should not have a rank, but should once it has been disaggregated.
IF [Parameter]="All" THEN "–" ELSE [Rank]
END