DAX : Ranking the Stars with RankX

Introduction

I have to work on a ranking problem and therefore I decided to study the RANKX() function in DAX a bit further. The idea is that the RANKX function determines the rank of a certain item in a complete list of items.

I've used a blogpost of Rob Collie (RANKX-apalooza: Within and Across Groups, With Filters, Etc.) as a source for this blogpost.

RankX function

As stated in the RANKX MSDN help information, the RANKX function returns the ranking of a number in a list of numbers for each row in the table argument.

The structure of the DAX function is :

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

So there are five parameters:

• table. A DAX expression that returns a table.
• expression. This is the DAX expression that returns a scalar
• value. Any DAX expression that returns a single scalar value whose rank is to be found.
• order. Ascending or descending
• ties. How the numbering should go when there are ties.

The Data

I've created a data model with three tables: DimPeriod, DimProduct and a Fact Sales table and this is how they are connected:

This is the data in the FactSales table :

This is the data in DimPeriod table:

This is the data in the DimProduct table:

Let's begin

First create the explicit SUM expression that will be used in the other examples. And it seems necessary according to Kasper de Jonge: "The reason for that is that SUM(Sales[SalesAmount)) is not able to be calculated against each and every row for ALL(Regions[Region]) but will be evaluated against the total result. If you would wrap a CALCULATE around the measure it would be able to evaluate the value for each and every row. A measure is automatically wrapped around CALCULATE() by the engine."

     
NR:=SUM(FactSales[NeRevenue])

My first Rankx() function

The first RANKX expression was this one. It calculates the position of a product in a list.

       
OverallRankProduct:=IF(HASONEVALUE(DimProduct[ProductID]);
                                   RANKX(ALL(DimProduct); [NR]);
                                   BLANK()
                      )
       

The IF(HASONEVALUE(),…,BLANK()) test prevents from ranking subtotals as if they were Products.

In a first draft of my product table I had two of the same Productnames: 100 in the Chopper category and 100 in the plane category and that lead to a blank OverallRankStore record in a simple pivottable (on the right).

I replaced the HASONEVALUE with a check with the COUNTROWS.

 OverallRankProduct:=IF(COUNTROWS(VALUES(DimProduct[ProductName]))=1;
                        RANKX(ALL(DimProduct); [NR]);
                        BLANK()
                       )                        

I adjusted my DimProduct table in order to have unique productnames and here you can see a couple pivottables. A chopper 100 model is ranked first in 2016 and a Plane model 70 is ranked 8.

So this is ranking neglecting the hierarchies. It is ranking across the hierarchy.

Ranking with a hierarchy

Here is an expression with a ranking of products within a upper level in a hierarchy. In this case Category

       
OverallRankProductWithinCategory:=IF(HASONEVALUE(DimProduct[ProductID]);
                                     RANKX(ALL(DimProduct[ProductName]); [NR]);
                                     BLANK()
                                     )
 

And this is the result of 2016 and here you can see that in the Plane category product 747 is ranked first, 70 is ranked second and 500 is ranked third.

This is the result of 2017:

Now If I change the hierarchy to Brand it works too. For instance Boeing has two products 100 and 747 and they are ranked 1 and 2.

This is a printscreen for 2016

Ranking products with same names

Here again an example of a product with the same name and using the HASONEVALUE:

By using the IF HASONEVALUE it's not calculating the RANKX.

By using the following expression, the non unique record is shown and the Total is also empty.

       
OverallRankProductNonUnique2:=IF(COUNTROWS(VALUES(DimProduct[ProductName]))=1;
                                 RANKX(ALL(DimProduct); [NR])
                                )
 

So finally the following table shows different variations of the data

This pivottable is with MainCategory and Category:

And this one PivotTable is with brand.

Now If we want to see the ranking of the categories within the Maincategories, then we need another kind of expression.

OverallRankProductCategory:=IF(HASONEVALUE(DimProduct[Category]);
                               RANKX(ALL(DimProduct[Category]); 
                                     CALCULATE([NR]; ALL(DimProduct[MainCategory]))
                                    );
                               BLANK()
                             )
 

And this results in the following PivotTable:

Ranking based on a selected items in a slicer

It is also possible to ranked items based on the selection of slicers.

       
RankProductSEL:=IF(COUNTROWS(VALUES(DimProduct[ProductName]))=1;
                                                   RANKX(ALLSELECTED(DimProduct); [NR]);
                                                   BLANK()
                                                 )

And here you can see the result. I've only selected plane 300, 70 and 747 and these are ranked 3, 2 and 1.

Ranking all the stars

In the following example I've turnoff any filter and the products are ranked for every value in the table. Even the year is negleted.

 RankProductEver:=IF(COUNTROWS(VALUES(DimProduct[ProductName]))=1;
                       RANKX(ALL(DimProduct); CALCULATE([NR]; ALL(DimPeriod)));
                          BLANK()
                       )

Resulting in:

Conclusion

Interesting function to use in DAX to rank items on their own but also in a hierarchy.

Greetz,

Hennie