DAX RANKX Function in Power BI

Last Updated on: 1st July 2024, 02:30 pm

In the world of data analysis and business intelligence, ranking is a crucial operation that helps in understanding the relative position of items within a dataset. Power BI, with its powerful DAX (Data Analysis Expressions) language, offers the RANKX function as a versatile tool for performing ranking operations. This article will delve deep into the RANKX function, exploring its syntax and demonstrating its application through 12 practical examples.

Understanding RANKX

Unlike basic sorting operations, RANKX allows you to rank items based on complex expressions, considering ties, and even rank across different groupings. This flexibility makes it an indispensable tool for advanced data analysis in Power BI.

The RANKX Syntax

The basic syntax of the RANKX function is:

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

Where:

• <table> is the table or table expression to be ranked.
• <expression> is the expression to be evaluated for ranking.
• <value> (optional) is the row to be ranked. If omitted, the current row is used.
• <order> (optional) specifies the ranking order (0 for descending, 1 for ascending).
• <ties> (optional) specifies how to handle ties (0 for dense ranking, 1 for skip ranking).

Now, let’s explore 12 examples to illustrate the power and flexibility of RANKX.

12 Practical Examples of RANKX in Action

1. Basic RANKX: Ranking Sales

Consider a simple Sales table:

To rank products by sales:

Rank = 
RANKX(
    ALL(Sales),
    Sales[Sales],
    ,
    DESC
)

This formula ranks all products by their sales in descending order. The result would be:

2. RANKX with Ties: Dense Ranking

Using the same Sales table, let’s rank with dense ranking for ties:

DenseRank = 
RANKX(
    ALL(Sales),
    Sales[Sales],
    ,
    DESC,
    Dense
)

If we add another product with sales of 1200:

3. RANKX with Grouping: Ranking Within Categories

Consider a table with sales by category:

To rank products within their category:

CategoryRank = 
RANKX(
    FILTER(
        ALL(Sales),
        Sales[Category] = EARLIER(Sales[Category])
    ),
    Sales[Sales],
    ,
    DESC
)

This formula ranks products within their respective categories.

4. RANKX with Calculated Measure: Ranking by Profit Margin

Assuming we have a calculated measure for profit margin:

Profit Margin = DIVIDE(SUM(Sales[Profit]), SUM(Sales[Sales]))

We can rank products by profit margin:

Profit Margin Rank = 
RANKX(
    ALL(Products),
    [Profit Margin],
    ,
    DESC
)

This ranks products based on their profit margin rather than raw sales.

5. RANKX with Time Intelligence: Year-over-Year Ranking

To rank products based on their year-over-year growth:

YoY Growth Rank = 
RANKX(
    ALL(Products),
    DIVIDE(
        [Total Sales],
        CALCULATE(
            [Total Sales],
            SAMEPERIODLASTYEAR(Dates[Date])
        )
    ) - 1,
    ,
    DESC
)

This formula ranks products based on their sales growth compared to the same period last year.

6. RANKX with TOPN: Top 5 Products

To get the rank of only the top 5 products by sales:

Top 5 Rank = 
IF(
    [Sales Rank] <= 5,
    [Sales Rank],
    BLANK()
)

This formula uses a previously calculated [Sales Rank] and only shows the rank for the top 5 products.

7. RANKX with Running Total: Cumulative Percentage Rank

To calculate a cumulative percentage rank:

Cumulative % Rank = 
RANKX(
    ALL(Sales),
    CALCULATE(SUM(Sales[Sales])),
    ,
    ASC
) / COUNTROWS(ALL(Sales))

This formula provides a percentage rank based on cumulative sales.

8. RANKX with Parameterized Ranking: Dynamic Top N

Create a parameter table for N:

Then use it in a dynamic ranking measure:

Dynamic Top N Rank = 
VAR SelectedN = SELECTEDVALUE(Parameters[N])
RETURN
IF(
    [Sales Rank] <= SelectedN,
    [Sales Rank],
    BLANK()
)

This allows users to dynamically choose how many top products to rank.

9. RANKX with Moving Average: Trend Ranking

To rank products based on their 3-month moving average sales trend:

Trend Rank = 
RANKX(
    ALL(Products),
    CALCULATE(
        AVERAGEX(
            DATESINPERIOD(
                Dates[Date],
                MAX(Dates[Date]),
                -3,
                MONTH
            ),
            [Total Sales]
        )
    ),
    ,
    DESC
)

This ranks products based on their average sales over the last 3 months.

10. RANKX with SWITCH: Multi-Criteria Ranking

To rank products based on different criteria depending on their category:

Multi-Criteria Rank = 
RANKX(
    FILTER(
        ALL(Sales),
        Sales[Category] = EARLIER(Sales[Category])
    ),
    SWITCH(
        Sales[Category],
        "X", Sales[Sales],
        "Y", Sales[Profit],
        Sales[Units]
    ),
    ,
    DESC
)

This ranks products by sales for category X, profit for category Y, and units sold for all other categories.

11. RANKX with EARLIER: Comparative Ranking

To rank each product’s sales against the average sales of its category:

Category Performance Rank = 
RANKX(
    ALL(Products),
    DIVIDE(
        Sales[Sales],
        CALCULATE(
            AVERAGE(Sales[Sales]),
            ALLEXCEPT(Sales, Sales[Category])
        )
    ),
    ,
    DESC
)

This formula ranks products based on how their sales compare to the average sales in their category.

12. RANKX with CROSSFILTER: Ranking Across Related Tables

Assuming we have a Products table related to the Sales table:

Product Rank by Related Sales = 
RANKX(
    ALL(Products),
    CALCULATE(
        SUM(Sales[Sales]),
        CROSSFILTER(Products[ProductID], Sales[ProductID], Both)
    ),
    ,
    DESC
)

This ranks products based on their total sales, ensuring that the relationship between Products and Sales tables is considered.

Harnessing the Power of RANKX

The RANKX function in DAX is a powerful tool that allows for complex ranking operations in Power BI. From basic sales rankings to multi-criteria evaluations and time-intelligent comparisons, RANKX provides the flexibility needed to tackle a wide range of analytical challenges.

As you continue to work with Power BI and DAX, incorporating RANKX into your formulas will undoubtedly enhance your ability to extract meaningful insights from your data. Remember, the key to mastering RANKX lies in understanding its parameters and how they interact with your data model.

Whether you’re analyzing sales performance, evaluating product profitability, or tracking trends over time, RANKX will prove to be an invaluable tool in your DAX arsenal. Practice with these examples, adapt them to your specific data scenarios, and you’ll soon find yourself creating sophisticated ranking measures with ease.