How can values be ranked by group using Power BI?

---

You can use the following syntax in DAX to create a new column that displays the rank of values in one column, grouped by another column:

Points Rank = 
VAR current_team = 'my_data'[Team]
RETURN
    RANKX(
        FILTER(
            'my_data',
            'my_data'[Team] = current_team
        ),
        'my_data'[Points],
        ,
        ,
        SKIP
    )

This particular example creates a new column named Points Rank that assigns a ranking to each value in the Points column of the table, grouped by the values in the Team column.

The following example shows how to use this syntax in practice.

Example: How to Rank Values by Group in Power BI

Suppose we have the following table in Power BI named my_data that contains information about points scored by basketball players on various teams:

Suppose we would like to add a new column that displays the rank of the values in the Points column, grouped by the Team column.

To do so, click the Table tools tab along the top ribbon, then click the New column icon:

Then type in the following formula into the formula bar:

Points Rank = 
VAR current_team = 'my_data'[Team]
RETURN
    RANKX(
        FILTER(
            'my_data',
            'my_data'[Team] = current_team
        ),
        'my_data'[Points],
        ,
        ,
        SKIP
    )

This will create a new column named Points Rank that displays the rank of the values in the Points column, grouped by the Team column:

For example, we can see:

• The player with the most points on team A (39 points) received a rank of 1.
• The player with the second most points on team A (30 points) received a rank of 2.
• The player with the third most points on team A (22 points) received a rank of 3.

And so on.

Note that if you would instead like to rank from high to low, then you can specify the value 1 in the second to last parameter of the RANKX function:

Points Rank = 
VAR current_team = 'my_data'[Team]
RETURN
    RANKX(
        FILTER(
            'my_data',
            'my_data'[Team] = current_team
        ),
        'my_data'[Points],
        ,
        1,
        SKIP
    )

This will now assign a rank of 1 to the lowest value in each group, a value of 2 to the second lowest value in each group, and so on.

Note: You can find the complete documentation for the RANKX function in Power BI .

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI: