cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

classic RANKx issue

I am using AdventureWorks DB and I want to rank the resellers by their total sale amount. So basically the table looks like this:

rKs9E.png

 

I used the following measure to rank it:

Rank Reseller = RANKX(ALL(ResellerSales), MAX(ResellerSales[SalesAmount]),,1)

But it returns only 1 as the rank.

 

Q8.png

 

followed these two videos:

https://www.youtube.com/watch?v=z2qzJVeYhTY

and

https://www.youtube.com/watch?v=SsZseKOgrWQ&t=603s

 

but I can't understand what is wrong. 

 

Can anyone explain, please Smiley Sad

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: classic RANKx issue

@elahemeydani

The Measure I posted above works in the file you shared! Smiley Happy

In the File you uploaded you had the Reseller Key from the Reseller Sales table and that's why it works,

In the picture you posted above it did not work because you had the Reseller Key from the Reseller Table.

In that case you need to adjust the Measure to reference that table.

Rank Reseller 2 = RANKX ( ALLSELECTED ( Reseller[ResellerKey] ), CALCULATE ( SUM ( ResellerSales[SalesAmount] ) ), ,)

 

RANKX Works.png

Super User
Super User

Re: classic RANKx issue

@elahemeydani

 

 @Sean MEASURE works well in the file

 

But in the screen shot, i noticed that you are also including Reseller Name from another Table "Reseller"

 

If you include that other column, this MEASURE would work

 

 

Rank Reseller =
RANKX (
    SUMMARIZE (
        ALLSELECTED ( ResellerSales ),
        ResellerSales[ResellerKey],
        Reseller[ResellerName]
    ),
    CALCULATE ( SUM ( ResellerSales[SalesAmount] ) ),
    ,
)

advenrue.png

11 REPLIES 11
Super User
Super User

Re: classic RANKx issue

Hi @elahemeydani

 

try to wrap CALCULATE around your MAX function in order for context transition to happen

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Re: classic RANKx issue

@LivioLanzo

Dear Livio
I think this, is not the solution. Because, by wrapping it with calculation, the result makes no sense, because when I sort the SalesAmount column to an Desc Order, the first one should have the Rank 1, but it has rank 41 !!
rKs9E.png

 

Why?

 

 

Super User
Super User

Re: classic RANKx issue

I believe you also may want to use SUM and not MAX in your formula ?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Re: classic RANKx issue

@LivioLanzo
Basically, I have a reseller list and the saleAmount (which is the sale*quantity) for each reseller. 


Now I want to have them ranked in a way that the reseller with the highest SaleAmount receives the rank 1. 

I believe I should use neither MAX nor SUM. But the RANKx function didn't accept the columns without an aggregation function. 

 

Super User
Super User

Re: classic RANKx issue

How about this

Rank Reseller =
RANKX (
    ALLSELECTED ( ResellerSales[ResellerKey] ),
    CALCULATE ( SUM ( ResellerSales[SalesAmount] ) ),
    ,
)

Hope this helps! Smiley Happy

Super User
Super User

Re: classic RANKx issue

could you share your file?  The measure depends on the model and what is being used in the matrix

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Re: classic RANKx issue

@Sean
Thank you for your answer. I have already tested that, and it is not working :

Q9.png

Super User
Super User

Re: classic RANKx issue

@elahemeydani

The Measure I posted above works in the file you shared! Smiley Happy

In the File you uploaded you had the Reseller Key from the Reseller Sales table and that's why it works,

In the picture you posted above it did not work because you had the Reseller Key from the Reseller Table.

In that case you need to adjust the Measure to reference that table.

Rank Reseller 2 = RANKX ( ALLSELECTED ( Reseller[ResellerKey] ), CALCULATE ( SUM ( ResellerSales[SalesAmount] ) ), ,)

 

RANKX Works.png

Re: classic RANKx issue

@LivioLanzo

You can find the pbix file in the following link:

 

https://1drv.ms/u/s!AvIDRK7nxaXWtTVRXCwAgerjI_4H