Ranking using DAX in Power BI

Data source used for this post is the ‘Internet Sales’ data mart of the ‘AdventureWorksDW2017´ SQL server database.

In this example, let’s look at ranking Countries and States individually according to the sales and then ranking Country and State fields when grouped together. For ranking, RANKX function is being used. RANKX function helps to provide a rank for each row in a table according to the value field in the formula.

Let’s look at the steps involved in doing this.

1) Create the ‘Sales Amount’ measure from the ‘Internet Sales’ fact table using the DAX formula below.

Sales Amount = 
CALCULATE(
            SUM('Internet Sales'[SalesAmount])
        ) 

2) Create a new measure that calculates the rank of the Country according to the Sales. For this, use the DAX formula below.

CountryRank = 
 IF 
 ( 
     ISINSCOPE('Geography'[CountryRegionCode]),
     RANKX
     (
         ALLSELECTED('Geography'[CountryRegionCode]),
         [Sales Amount]
     )
 )

3) Verify the measure created using a table visualization with Country field, Sales amount measure and the newly created CountryRank measure. When you sort the visualization using the CountryRank, you could see the rank was given according the sales made. (Please note that ISINSCOPE function in the above formula helps to determine whether the Country hierarchy is in the context or not. When it’s not used, ‘Total’ in the table visualization would be having a rank.)

4) Similar to the CountryRank, create a measure to calculate the rank of each State according to the Sales. To do this, use the DAX measure below.

StateProvinceRank = 
 IF 
 ( 
     ISINSCOPE('Geography'[StateProvinceName]),
     RANKX
     (
         ALL('Geography'[StateProvinceName]),
         [Sales Amount]
     )
 )

5) As a next step, when Country and State are grouped, if the country should be ranked according to the sales made and the States in a Country should be ranked separately, ranking formulas from above doesn’t work correctly. To resolve this issue, create new measure using the DAX formula below.

CountryStateRank = 
 IF 
 ( 
     ISINSCOPE('Geography'[CountryRegionCode]) && 
             ISINSCOPE('Geography'[StateProvinceName])
     ,[StateProvinceRank]
     ,IF 
     ( 
         ISINSCOPE('Geography'[StateProvinceName]),
         [StateProvinceRank],
         [CountryRank]
     )
 )  

6) Verify the measure created using a matrix visualization with ‘Country’ and ‘State’ in rows section and the Sales Amount measure and the CountryStateRank DAX measure from the above step in the values section. When you sort the matrix using the CountryStateRank field, could see the Countries are given ranks according to the sales made and then States are ranked in each Country according to the sales made when drilled down to next level and also works fine when traversing through each level.

Hope this post helps you!