Sorting

[Ania26]

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],,DESC,Dense))
sorting by value from recent year and then Others at the end. So for this chart:

Spain should be before Poland. 

[DataNinja777 · Accepted solution]

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]

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.

[Kishore_KVN]

Hello @Ania26 , It sorts based on total. Please mention Year level filtering in your TOPN and that will help you to bring Spain above Poland. 

Mark this comment as answer if it helps you so that others can reach quickly. Thanks.