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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
RexaZii93
Helper I
Helper I

How to create ranking for highest value to lowest

Hi

 

I want to create a rank by highest value, for example if I have this table:

 

Routes:Total Pax No.:
RUH10,000
JED6,000
RUH15,000
ABH9,000
JED7,000

 

I want to create a rank by routes pax no (highest to lowest), like this example:

 

Routes:Total Pax No.:Rank:
RUH10,0001
JED6,0002
RUH15,0001
ABH9,0003
JED7,0002

 

which shows RUH is 1 as have the highest value, JED is 2 and the lowest is ABH as 3.

 

1 ACCEPTED SOLUTION
Shishir22
Solution Sage
Solution Sage

Hello @RexaZii93 ,

 

Try creating below calculated column- 

Rank =
RANKX (
    'Table',
    CALCULATE (
        SUM ( 'Table'[Total Pax No.] ),
        FILTER ( 'Table', 'Table'[Routes] = EARLIER ( 'Table'[Routes] ) )
    ),
    ,
    DESC,
    DENSE
)

 

Shishir22_0-1654582099392.png

 

Please mark it as solution if it solves your issue. Kudos are also appreciated.

 

 

Cheers,
Shishir

View solution in original post

7 REPLIES 7
Shishir22
Solution Sage
Solution Sage

Hello @RexaZii93 ,

 

Try creating below calculated column- 

Rank =
RANKX (
    'Table',
    CALCULATE (
        SUM ( 'Table'[Total Pax No.] ),
        FILTER ( 'Table', 'Table'[Routes] = EARLIER ( 'Table'[Routes] ) )
    ),
    ,
    DESC,
    DENSE
)

 

Shishir22_0-1654582099392.png

 

Please mark it as solution if it solves your issue. Kudos are also appreciated.

 

 

Cheers,
Shishir

Thanks, it worked! but I have a column which filter internations and domestic routs and another one which filters departure and arrival, when I apply it show the rank of international for example it miss up the rank and goes 7 10 30 etc.

can I make the rank dynamicly to when I apply filters.

 

Thanks

In that case, you need to go for Measure in place of calculated column.

 

Create measure - 

TotalPaxMeasure = Sum('Table'[Total Pax No.])

 

Create one more measure after creating above one and then use it for ranking.

 

RankMeasure =
CALCULATE (
    RANKX ( ALLSELECTED ( 'Table'[Routes] ), [TotalPaxMeasure],, DESC, DENSE )
)

 

Please mark it as answer if it resolves your issue. kudos are also appreciated.

 

 

Cheers,
Shishir

Thanks I added the 2 measures but how does work? do I just add the pax no. and the rank measures to the filtering option?

Just use rank measure as visual level filter and set to equal to 1.

 

Cheers,
Shishir

Thanks I tested to a list visual and it worked I can just change the number to any rank and it shows the data I want, but when I use a card visual  and applied the measure it grays out do you know why is that and how to fix it?

 

Screenshot (59).png

Anonymous
Not applicable

Hi @RexaZii93 

 

You can use this dax formula to create a new calculated column

 

Rank =
RANKX (
Table2,
CALCULATE (
SUM ( Table2[Total Pax No] ),
ALLEXCEPT (
Table2,
Table2[Routes]
)
),
,
DESC,
DENSE
)

 

This is the output of teh calculated column

esha_shah2002_0-1654581816463.png

 

If this post helps, Accept it as a solution.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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