cancel
Showing results for
Did you mean:

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

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.: RUH 10,000 JED 6,000 RUH 15,000 ABH 9,000 JED 7,000

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

 Routes: Total Pax No.: Rank: RUH 10,000 1 JED 6,000 2 RUH 15,000 1 ABH 9,000 3 JED 7,000 2

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

1 ACCEPTED SOLUTION
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
)``````

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

Cheers,
Shishir
7 REPLIES 7
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
)``````

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

Cheers,
Shishir
Helper I

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

Solution Sage

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 )
)``````

Cheers,
Shishir
Helper I

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?

Solution Sage

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

Cheers,
Shishir
Helper I

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?

Anonymous
Not applicable

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

If this post helps, Accept it as a solution.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors