Forum Discussion

Ania26's avatar
Ania26
Icon for Helper IV rankHelper IV
10 months ago
Solved

Sorting

Hello, I would like to have this measure: Rank Total C = IF('X'[TOP N Countries TC]<> BLANK(),RANKX(TOPN(SELECTEDVALUE('TOP N Selection'[Value]),ALLSELECTED('X'),[Total Total C]),[Total Total C],,D...
Need Help
  • DataNinja777's avatar
    DataNinja777
    10 months ago

    Hi Ania26 ,

     

    You can use the 2025 year value to sort the bar chart in the descending order by creating a sorting measure like below and putting in the tooltip field.  

     

    Rank by Latest Year = 
VAR CurrentCountry = SELECTEDVALUE('X'[Country]) // <-- Replace with your country column
VAR RecentYearValue = calculate([Total Amount],'Calendar'[Year]=2025) // <-- Replace with your 2025 value measure

RETURN
IF(
    CurrentCountry = "Other",
    -999999999, // A very large negative number to push "Other" to the last position
    RecentYearValue
)

    Then you can click on the three dot elipsis .... and select the measure above.

    The resultant out put is as shown above which is in line with your requirement.  

     

    I am attaching an example pbix file for your reference.

     

    Best regards,

     

     

     

Ilgar_Zarbali's avatar
Ilgar_Zarbali
Icon for Super User rankSuper User
10 months ago

Here’s a simple sort-key measure that ranks by the latest selected year and always pushes “Other” to the end:

 

Sort (RecentYear, Other last) :=
VAR RecentYear =
MAXX ( ALLSELECTED ( 'Date'[Year] ), 'Date'[Year] )
VAR ValRecentYear :=
CALCULATE ( [Total Total C], KEEPFILTERS ( 'Date'[Year] = RecentYear ) )
VAR IsOther = SELECTEDVALUE ( 'X'[Country] ) = "Other"
VAR RankRecent :=
RANKX (
ALLSELECTED ( 'X'[Country] ),
CALCULATE ( [Total Total C], KEEPFILTERS ( 'Date'[Year] = RecentYear ) ),
, DESC, DENSE
)
RETURN IF ( IsOther, 100000 + RankRecent, RankRecent )

 

Use this measure to Sort by in the visual (ellipsis › Sort by › Sort (RecentYear, Other last)).
If you also apply a Top-N, use the same RANKX expression but replace ALLSELECTED('X'[Country]) with your Top-N table.