Not able to comprehend working of Rankx function in DAX

[ARU_]

Hello,

I am quite new to DAX and still learning various aspects of this language. While working on contoso database, i wanted to compute rank based on category-wise sales, even if the granularity of the table is deeper than that.  

Following is the table i want to build. The granularity of this table is at Product code level while my computation supposedely should happen at a product category level.

My  understanding is that Rankx creates a lookup table by iterating an expression as the 2nd parameter and rank the lookup table values based on the order (ASC or DESC depending on 4th parameter). The 3rd parameter which is an expression gets evaluated as per the current row context. 

I created this following measure in order to arrive at the rank of the category sales

category wise ranking := RANKX(ALL('Product'[Category]), 
                CALCULATE(SUMX('Product',[Sales Amount])),  -- Invokes context transiiton
                [category wise sales])

the dependent measures are as follows :

category wise sales := CALCULATE(SUMX('Product', [Sales Amount]), ALLEXCEPT('Product','Product'[Category]))

Sales Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

This output gives me the ranking of "1" in all the rows of Power BI table as per the screenshot above. However, i expected it to give me the rank based on category wise sales. So "Music, Movies and Audio Books" category should have "8" as the rank, while "Home appliances" should carry a rank of "1"

I think the above DAX code works in steps given below :

Step 1. For each row of Power BI table view, it evaluates the first parameter (table expression). As the table has an outer function "All", it returns all the values of the Product category as a table. 

Step 2. The 2nd expression iterates through the table output from Step no.1 above (all categories) and gets category wise sales through context transition. These category wise sales act as a lookup table for ranking

3. The 3rd expression gets evaluated in the current context and returns category wise sales against each row of the powerbi table view. This scalar value gets compared with the lookup table values and generate a ranking.

I am sure that there is something wrong in my understanding of the steps. above as the output is different from the desired result.  

Having said that, I can achieve the desired result result through following measure. But i am not sure why my previous piece of code generates incorrect result.  

category wise ranking := RANKX(ALL('Product'[Category]), 
                [category wise sales])

Desired output as below :

One can find the PBIX in the following link (Refer Page 3)

https://drive.google.com/file/d/185eE645t2I0EX9joWTwnQ87pt9cLp0I2/view?usp=sharing

Thank you everyone for your help. 

Regards
ARU

[tamerj1]

Hi @ARU_ 
Please refer to attached sample file with the proposed solution

category wise ranking2 = 
RANKX ( 
    ALL ( 'Product'[Category] ), 
    CALCULATE ( 
        [Sales Amount], 
        ALLEXCEPT ( 'Product', 'Product'[Category] ) 
    ) 
)

[ARU_]

Hi @tamerj1  - Thanks for your help. 

I know that this alternate "category wise ranking 2" measure work. The reason i dont understand why my original measure do not work. 

I basically understood and referred the logic that DAX takes in order to compute the Ranking from both microsoft documentation and dax.guide. I also went through the help link which @ValtteriN shared. After referring these available help documents and similar questions people have asked in community, i am still unsure of why this original measure of mine do not work. 

category wise ranking := RANKX(ALL('Product'[Category]), 
                CALCULATE(SUMX('Product',[Sales Amount])),  -- Invokes context transiiton
                [category wise sales])

the dependent measures are as follows :

category wise sales := CALCULATE(SUMX('Product', [Sales Amount]), ALLEXCEPT('Product','Product'[Category]))

Sales Amount := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

In "category wise ranking" measure,

> I have made use of 2nd parameter of Rankx based on which the ranking values gets determined

and

> also used 3rd parameter (optional) whose scalar value for a table cell gets compared with all the values from the 2nd parameter and get a rank value depending on the sort order.  

It doesn't work.

I am sure that there is someplace where i am going wrong and misunderstanding rankx working. Not able to figure out that though. 

Thank you and Regards

ARU

[ValtteriN]

Hi,

The general approach I recommend when it comes to RANKX is to first test your measure which will determine the rank in row context. In this case it would be this part of your dax:

 CALCULATE(SUMX('Product',[Sales Amount])),  -- Invokes context transiiton
                [category wise sales])

I will describe an example here:

I am ranking Average % on my test data with a condition that amount of transactions needs to be more than 30:

Ranking = if([Average metric]<>0,RANKX(ALLSELECTED(Ranking), [Average metric]),"Excluded from ranking")

Here the base measure is [Average metric] 

Average metric = if(sum(Ranking[Amount of transactions])>=30,AVERAGE(Ranking[Average]),0)

When placed to a table this looks as follows:

Here we can see that [Average metric] is working as expected. 

The dax for ranking in my example is this:

RANKX(ALLSELECTED(Ranking), [Average metric])

Since we have verified that [Average metric] works as expected there isn any further need to include SUMX or other calculations in RANKX. In your case I recommend creating a measure which will return the values you are expecting to see and only afterwards wrap it inside RANKX function.

Also since you are dealing with contoso data I recommend reading this article: Introducing RANKX in DAX - SQLBI


I hope this post helps to solve your issue and if it does consider a...

https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[ARU_]

Hi @ValtteriN  - Thanks for helping me out here. 

I tried what you said. However it doesnt seem to yield the desired result. 

I created a table with Category and sum_of_sales like below just to check that the lookup table which is used for ranking works as desired. 

In this case, the category wise sales provides the values that i want to use for ranking.  

I also made changes in the rank measure and replaced the expression with measure reference. 

category wise ranking = RANKX(ALL('Product'[Category]), 
                [sum_of_sales],  -- Invokes context transiiton
                [category wise sales])

Following is the measure DAX

sum_of_sales = CALCULATE(SUMX('Product',Sales[Sales Amount]))

The result do not change. 

Not sure where i am lacking the required DAX understanding

Regards

ARU