Tableau
Tableau Funtion: RANK_MODIFIED( )
Tableau Function: RANK_MODIFIED( )
Category: Table Calculation Functions
Purpose
The RANK_MODIFIED() function in Tableau is a table calculation that assigns a modified rank to values within a partition. It handles ties differently from RANK() and RANK_DENSE() by assigning the same rank to tied values, but not skipping as many rank positions as standard ranking.
In simple terms, RANK_MODIFIED() answers:
“What is this value’s rank when ties should advance the next rank by one?”
Type of Calculations
Table calculations
Order-based ranking
Tie-aware ranking
Post-aggregation comparative analytics
Like other ranking functions, RANK_MODIFIED() is evaluated after aggregation and depends on sorting and partitioning.
Practical Use Cases
Ranking data when ties should be grouped but numbering should remain intuitive
Leaderboards where tied values should not create large rank gaps
Business reporting that requires simplified ranking logic
Competitive analysis with frequent ties
Cleaner ranking outputs for dashboards and tables
RANK_MODIFIED(expression, [order])
| Parameter | Type | Description |
|---|---|---|
| expression | Aggregate / table calculation | The numeric value to rank. Must be aggregated in the view. |
| order (optional) | Scalar (string) | Sort direction: "asc" for ascending or "desc" for descending. Default is "desc". |
How It Works?
Logical Principle
RANK_MODIFIED() assigns rank values based on unique positions rather than row counts:
Modified Rank = position of the distinct value in sorted order
Key behaviors:
Default ranking is descending
Ties share the same rank
Rank numbers progress sequentially without large gaps
Comparison with Other Ranking Types
| Function | Tie Handling | Rank Gaps |
|---|---|---|
| RANK() | Ties share rank | Gaps exist |
| RANK_DENSE() | Ties share rank | No gaps |
| RANK_MODIFIED() | Ties share rank | Minimal gaps |
What Does It Return?
Data Type: Integer
Meaning:
Returns
1for the top-ranked valueTied values receive the same rank
The next rank increases by 1, regardless of the number of ties
Example Output
| Value | RANK_MODIFIED() |
|---|---|
| 500 | 1 |
| 400 | 2 |
| 400 | 2 |
| 300 | 3 |
When Should We Use It?
Use RANK_MODIFIED() when you need to:
Rank values with frequent ties
Avoid skipped rank numbers caused by standard ranking
Present rankings clearly to business users
Balance between
RANK()andRANK_DENSE()behaviorMaintain intuitive rank progression
Basic Usage
Rank values using modified ranking
RANK_MODIFIED(SUM([Sales]))
- Highest sales receive rank 1
- Tied sales share ranks without large gaps
Column Usage
Ascending modified rank
RANK_MODIFIED(SUM([Profit]), "asc")
Lowest profit receives rank 1
Filter Top 5 modified ranks
RANK_MODIFIED(SUM([Sales])) <= 5
Ensures consistent Top-N results
Advanced Usage
Highlight top performers
IF RANK_MODIFIED(SUM([Sales])) <= 3 THEN "Top Performer"
ELSE "Others"
END
Useful for dashboards and KPI tables
Rank within partitions
RANK_MODIFIED(SUM([Sales]))
(with Compute Using set by Region)
Produces rankings independently within each group
Tips and Tricks
Always verify sort order and Compute Using
Use
RANK_MODIFIED()when stakeholders dislike skipped ranksCombine with parameters for interactive Top-N dashboards
Ranking results change if sorting changes
Not usable in row-level or LOD calculations
Related Functions
Functions commonly used alongside or as alternatives to RANK_MODIFIED():
RANK()RANK_DENSE()RANK_UNIQUE()INDEX()FIRST()WINDOW_SUM()
We’ve got plenty of resources to help you master Tableau functions. For more details, check out the official Tableau documentation. Or, if you’re ready for more practice, let’s dive into related functions and build your Tableau skills further!
If you’re ready to harness the full power of Tableau and elevate your data analytics capabilities, our expert Tableau consulting services are here to guide you. Whether you need support with building advanced calculated fields, creating dynamic visual dashboards, or optimizing your data sources for peak performance, our team of experienced Tableau consultants delivers customized solutions designed for your business needs. Visit our Tableau Consulting page to discover how we can help your organization turn data into impactful, insight-driven decisions.
1. What does RANK_MODIFIED() do in Tableau?
It assigns ranks to values while grouping ties and advancing ranks sequentially.
2. How is RANK_MODIFIED() different from RANK()?
RANK_MODIFIED() reduces rank gaps caused by ties, while RANK() skips ranks.
3. How is RANK_MODIFIED() different from RANK_DENSE()?
RANK_DENSE() never skips ranks, while RANK_MODIFIED() may skip minimally.
4. Is RANK_MODIFIED() a table calculation?
Yes, it is evaluated after aggregation and depends on the view layout.
5. When should I use RANK_MODIFIED()?
Use it when you want clear rankings without large gaps caused by tied values.