Ranking metrics

Many reports and analyses are built around the ranking of dimensions relative to measures, such as the top 10 salespeople based on YTD sales. Ranking measures can also help deliver more clean and intuitive report visualizations as they substitute small integer values for large numbers and decimal places. Ranking measures can be as simple as specifying a column and a measure, or more complex with unique ranking logic applied in distinct filter contexts.

Ranking measures in DAX are implemented via the RANKX() function, which is an iterator like SUMX() and FILTER(). As an iterating function, two required input parameters include a table and the expression to be evaluated for each row of the table. The following two measures rank products based on the Internet Net Sales measure:

Internet Net Sales Product Rank = 
RANKX(ALL('Product'[Product Alternate Key]),[Internet Net Sales],,DESC,Skip)

Internet Net Sales Product Rank (All Products) = 
VAR ProdRankTable = 
ALL('Product'[Product Alternate Key],'Product'[Product Name],'Product'[Product Category Group],'Product'[Product Category],'Product'[Product Subcategory],'Product'[Product Name])
RETURN
RANKX(ProdRankTable, [Internet Net Sales],,DESC,Skip)

As with date intelligence and other measures, ALL() is used to remove the filters applied to a table. The ALL() function both removes a filter and returns a table which can then be evaluated by other functions. ALL() can remove filters from an entire table, multiple columns of the same table, or a single column from a table. Additionally, the ALLEXCEPT() function can be used remove filters from the current and any future columns of a table, except for one or a specific set of columns.

In the Internet Net Sales Product Rank measure, the ALL() function returns a table of the unique product's alternate key values. Since only a single column is referenced by ALL() in this measure, other columns from the Product dimension table are allowed into the filter context. For example, in the following table, the Product Category column impacts the Internet Net Sales Product Rank measure so that the HL-U509-R product is ranked first given that it's the highest selling product in the Accessories category:

Ranking measures

The Internet Net Sales Product Rank (All Products) measure, however, ranks the product relative to all other products including products in the Bikes category. The group of columns specified in the ALL() function (the table parameter to RANKX()), defines the set of rows that the ranking expression will be evaluated against.

For ranking and certain other scenarios, it's necessary to apply alternative logic for subtotals. For example, the total row of the previous table visual would show a ranking value of 1 without any modification to the DAX. A common pattern to address subtotal values is to check whether an individual item of a column is in the filter context via HASONEVALUE(). The following revised measure uses an IF() conditional function to apply the ranking for individual products, but otherwise returns a blank value:

Internet Net Sales Product Rank = IF(HASONEVALUE('Product'[Product Alternate Key]),
RANKX(ALL('Product'[Product Alternate Key]),[Internet Net Sales],,DESC,Skip),BLANK())

As shown in this example, it's essential to understand the intended ranking logic and it may be necessary to store alternative ranking measures to suit the requirements of different reports and projects.

The RANKX() function has five parameters, but only the first two—the table and the expression to evaluate—are required. In this example, the third parameter is skipped via the comma and the measure is set to rank in descending order of the expression. Additionally, the final parameter (Skip or Dense) determines how tie values are treated. For example, if two products are tied for the highest sales, both products will be ranked 1, and the next-highest product will be ranked 3. Descending order and the skip tie behavior are both defaults, but it's a good practice to explicitly define these settings in the measures.

Table of Contents for Ranking metrics

Create new playlist

Sign In

Sign Up

Table of Contents for
Ranking metrics