Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 😞

 

2 ACCEPTED SOLUTIONS
Sean
Community Champion
Community Champion

@Anonymous

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

View solution in original post

@Anonymous

 

 @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


Regards
Zubair

Please try my custom visuals

View solution in original post

11 REPLIES 11
LivioLanzo
Solution Sage
Solution Sage

Hi @Anonymous

 

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!  

Anonymous
Not applicable

@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?

 

 

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!  

Sean
Community Champion
Community Champion

How about this

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

Hope this helps! Smiley Happy

Anonymous
Not applicable

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

Q9.png

Sean
Community Champion
Community Champion

@Anonymous

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

@Anonymous

 

 @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


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@Zubair_Muhammad and @Sean and @LivioLanzo
Thank you so much for your amazing support. 

Now, the measure is working. Let's cross our fingers that I can figure RANKx out for the next examples as well

Anonymous
Not applicable

@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. 

 

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!  

Anonymous
Not applicable

@LivioLanzo

You can find the pbix file in the following link:

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.