Forum Discussion

neeraj068's avatar
neeraj068
New Member
5 years ago
Solved

Issue with RANKX Function

Hi Team,

 

We are facing an issue when using the RANKXX function to calculate the Rank. We have a dataset that contains the Hotel Sales at each hotel that falls in a State. When all the Hotels or all the States are selected through the slicer, RankXX works fine but when only one state is selected through the slicer, even though Rank should be 1 for one particular state but it displays it as 2.

Measures Definition area as follow 

 

SRS_New = IF( HASONEVALUE(ParkNames[Park Codes-Park Name]),BLANK(),Sales_aggregateData[Sales Rank By State_new])

 

Sales Rank By State_new = IF (
ISBLANK(Sales_aggregateData[Booking_Total_SUM]),
BLANK(),CALCULATE(RANKX (ALL(States[States]),Sales_aggregateData[Booking_Total_SUM],,DESC),ALL(ParkNames[Park Codes-Park Name])))

 

Below is the sample data

 

State  ParkName Booking_Total_SUM

ABC   ABC_123    100

ABC   ABC_456    150

DEF   DEF_123    50

ABC   DEF_456    70

 

 

Please let us know if any more information is needed.

 

Thanks,

Neeraj 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  neeraj068 ,

     

    Modify your measure as below:

    Measure = 
RANKX(ALLSELECTED('ParkNames'),CALCULATE(SUM('ParkNames'[Booking_Total_SUM])),,ASC,Dense)

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

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

3 Replies

Replies have been turned off for this discussion
  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, neeraj068 

    I am not sure if I correctly understand your DAX measure, but please try to use ALLSELECT , instead of ALL, in front of (States[States]).

    When you want to filter the ranking by the states-slicer, then I believe ALLSELECT is the correct option.

    I hope this helped.

    Or, please kindly share your sample pbix file then I can try to have a look and come up with a desirable solution.

     

    Thank you very much.

     

     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi  neeraj068 ,

     

    Modify your measure as below:

    Measure = 
RANKX(ALLSELECTED('ParkNames'),CALCULATE(SUM('ParkNames'[Booking_Total_SUM])),,ASC,Dense)

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

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