How to use RANKX Function in Power BI

RANKX Function is Power BI
The DAX language offers the RANKX function that provides the ranking of an element sorted by using a certain expression. it is a scalar function and it is also an iterator.
Syntax :RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]]) 
table : Any DAX expression that returns a table of data over which the expression is evaluated.
expression : Any DAX expression that returns a single scalar value. The expression is evaluated for each row of table, to generate all possible values for ranking. 
value : (Optional) Any DAX expression that returns a single scalar value whose rank is to be found. We can ignore this (,,).
When the value parameter is omitted, the value of expression at the current row is used instead.
order : A value that specifies how to rank value, low to high or high to low.
0 or FALSE for Ranking in descending order of values of expression. If value is equal to the highest number in expression then RANKX returns 1.
1 or TRUE, for Ranking in ascending order of expression. If value is equal to the lowest number in expression then RANKX returns 1.
Suppose we have the data model in our Power BI report as follows..

Ranking Method - 1: One Dimension Table and a Fact Table
First we will create a measure "Total_Sales", that I will further use in the RANKX Function.
Total_Sales = SUMX(FactSales,FactSales[Net_Sales])
Now we will create the measure of RANKX , to show the Rank by the Country 

RankByCntry = RANKX(ALL(DimRegion[Country]),[Total_Sales],,0)

Note:

here [Total_Sales] is pre calculated measure on the related table FactSales[Net_Sales]

The RankX Function evaluating [DimRegion] table's [Country] dimension and applying the rank for [Total_Sales] by the  [Country] dimension.

We can also write the above expression(s) as below

RankByCntry = RANKX(ALL(DimRegion[Country]),SUMX(RELATEDTABLE(FactSales),[Net_Sales]),,0)

Output:

Suppose if we add a another dimension "Region_Name" from same "DimRegion" table to the above tabular report, then the output will be as follows..

We observed that the Rank is still applied to "Country" but it is break down by the "Region_Name"

To avoid the break down of the Rank, we have to use below Rank Expression.

Here the entire dimension table DimRegion is evaluated to apply the Rank.
RankByCntry = RANKX(ALL(DimRegion),SUMX(RELATEDTABLE(FactSales),[Net_Sales]),,0)

OR

RankByCntry = RANKX(ALL(DimRegion),CALCULATE(SUMX(FactSales,[Net_Sales])),,0)

Output :

Ranking Method - 2: Two Dimension Tables and a Fact Table

In this Ranking method, we  will evaluate two Dimension tables "DimRegion", "DimProducts" and the we apply Rank for "[Net_Sales]" of the related table "FactSales".

Rank_Cntry_Prod = RANKX(CROSSJOIN(ALL(DimRegion),ALL(DimProducts)),

SUMX(RELATEDTABLE(FactSales),[Net_Sales]),,0)

Output :

Ranking Method - 3: Applying Rank based on the Slicer Selection (One Dimension Table and a Fact Table)

Suppose if we wants to apply the Rank based on the Slicer selection. If we observe the below, where the Rank is not changed according to Slice Selection.

To apply the Rank according to Slicer Selection, we have to use the following DAX Expression:

RankbySelect = IF(ISBLANK(SUMX(RELATEDTABLE(FactSales),[Net_Sales])),BLANK(),
RANKX(
ALLSELECTED(DimRegion),
SUMX(RELATEDTABLE(FactSales),[Net_Sales]),,0))

Output:

Ranking Method - 4: Applying Rank based on the Slicer Selection (Two Dimension Tables and a Fact Table)

Suppose if we use two Slicers from two Dimension tables,  to apply the Rank by the "Net_Sales" of the Related Table "FactSalels" , we have to use the following expression :

RankBySelect_ = IF(ISBLANK(SUMX(RELATEDTABLE(FactSales),[Net_Sales])),BLANK(),

RANKX(
CROSSJOIN(ALLSELECTED(DimRegion),ALLSELECTED(DimCustomers)),
SUMX(RELATEDTABLE(FactSales),[Net_Sales]),,0))

Output :

Note:

To avoid the Ranking for Blank values, we have to use the "ISBLANK(SUMX(RELATEDTABLE(FactSales),[Net_Sales]))" in the expression to return "BLANK()" that avoids the Ranking.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------