Dax Function: RANK.EQ
Category: Statistical Functions
The RANK.EQ function in Power BI is a DAX function that computes the rank of a value within a dataset, with ties assigned the same rank. It’s useful for determining the position of values in a sorted list.
Purpose of the Function
To rank data points in ascending or descending order.
Type of Calculations
Positional analysis within a dataset.
Practical Use Cases
- Ranking sales figures across regions or representatives.
- Determining the top-performing products in a category.
- Creating rank-based visualizations like leaderboards.
RANK.EQ(<expression>, <table>, [<order>])</order></table></expression>
| Parameter | Type | Description |
|---|---|---|
<Expression> | Scalar | The value or column to rank. |
<Table> | Table | The dataset or group of values to rank within. |
[<Order>] | Boolean | (Optional) Sorting order: 0 (or omitted) for descending, 1 for ascending. |
How Does RANK.EQ Dax Works
Mathematical Principle
The function evaluates the position of a value in a list sorted based on the given order:
For descending order: Highest value gets rank 1.
For ascending order: Lowest value gets rank 1.
Equal values receive the same rank, and the subsequent ranks skip accordingly.
Example:
Consider the dataset: {50, 40, 40, 30}
Descending order: Ranks are {1, 2, 2, 4}.
Ascending order: Ranks are {4, 2, 2, 1}.
What Does It Return?
Returns a scalar value representing the rank of the specified
<Expression>within the<Table>.Ties are assigned the same rank, skipping subsequent numbers.
When Should We Use It?
Competitive Analysis: Ranking sales, profits, or performance metrics.
Segmentation: Identifying top and bottom performers.
Dynamic Visualizations: Creating ranks for slicers or filters in dashboards.
Examples
Basic Usage :
Rank sales figures in descending order:
RANK.EQ(Sales[TotalSales], Sales, 0)
Output: The highest total sales receive rank 1.
Column Usage
Rank sales by product dynamically:
ADDCOLUMNS(
Products,
"SalesRank",
RANK.EQ(Products[Sales], Products, 1)
)
Use Case: Adds a column SalesRank to display the rank of each product based on sales in ascending order.
Advanced Usage
Combine with filters to rank within a subset:
RANK.EQ(
CALCULATE(SUM(Sales[TotalSales]), Sales[Region] = "North"),
Sales,
0
)
Use Case: Ranks total sales for the “North” region only.
Tips and Tricks
Custom Order: Use calculated columns or measures to rank by complex expressions.
Avoid Gaps: If you need continuous ranks without skipping, consider using alternative ranking logic.
Combine with Filters: Filter the dataset for more specific rankings (e.g., by region or category).
Performance Impact of RANK.EQ DAX Function:
Ensure the
<Table>parameter is appropriately filtered to avoid ranking irrelevant data.Using large datasets with multiple calculations may impact performance; pre-aggregate where possible.
Related Functions You Might Need
RANKX: Similar to
RANK.EQbut offers more flexibility in defining the context and logic for ranking.TOPN: To retrieve the top N items from a dataset.
PERCENTILE.INC: For percentile-based rankings.
Want to Learn More?
For more information, check out the official Microsoft documentation for RANK.EQ You can also experiment with this function in your Power BI reports to explore its capabilities.
Experience the transformative power of Power BI by turning your data into valuable, insight-driven outcomes with the expertise of our seasoned consultants. Whether you’re looking for support with complex DAX calculations, designing engaging and easy-to-use dashboards, or improving data model performance, our Power BI experts deliver solutions tailored to your organization’s specific needs. Explore our Power BI Consulting Services page to learn how we can help your business make more intelligent, data-focused decisions.
It computes the rank of a value within a dataset, assigning the same rank to ties.
Ties are assigned the same rank, and the next rank skips the number of tied values.
RANKX provides more control over ranking logic and evaluation context.
Yes, use filters or calculated measures to rank within specific subsets.
Use 0 for descending order (default) and 1 for ascending order.