Power BI sorting - Keep specific row at the bottom of the table when sorting by value

[PVS07]

I would like to create a table in Power BI where I would to keep one specific row at the bottom regardless of the value.

For example, I want a table with top 15 countries in descending order and the all the other countries that have been grouped to "Other countries" as the last row. When I tried to create a table in Power BI the "Other countries" shows up in fourth position based on the value, however I would like to have it as the last row. Please see the screenshot below for reference.

Can anyone please let me know if it is possible to do this in Power Bi and how to do this. Thank you.

Sample data:

Country	Number
Country 1	1,213
Country 2	1,086
Country 3	718
Country 4	448
Country 5	394
Country 6	186
Country 7	171
Country 8	133
Country 9	131
Country 10	121
Country 11	92
Country 12	67
Country 13	45
Country 14	39
Country 15	39
Other countries	466

[v-angzheng-msft · Accepted solution]

Hi, @PVS07 

You can create a sorted table, then establish a one-to-many relationship between the sorted table and the main table, use the RELATED function to create a sort column in the main table, and finally make the country column sort by the sort column.

refer:

https://community.powerbi.com/t5/Desktop/sort-months-into-chronological-order/m-p/2007639#M755993

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tschris]

An alternative solution to short the Other option I have implemented and added the code and the files here

[v-angzheng-msft · Accepted solution]

Hi, @PVS07 

Sorry for not understanding your question correctly before😫

Try to create a measure like this:

_rank =
VAR _rank =
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[Country] <> "Other countries" ),
        CALCULATE ( SUM ( [Number] ) ),
        ,
        ASC,
        DENSE
    )
VAR _other =
    IF ( MAX ( 'Table'[Country] ) = "Other countries", 0, _rank )
RETURN
    _other

result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PVS07]

Thanks for the detail and the calculated measure. 

[v-angzheng-msft]

Hi, @PVS07 

May I ask if your problem has been solved? Is the above post helpful to you?
If it helps, could you please mark the post which help you as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.😀

Best Regards,
Community Support Team _ Zeon Zheng

[v-angzheng-msft · Accepted solution]

Hi, @PVS07 

You can create a sorted table, then establish a one-to-many relationship between the sorted table and the main table, use the RELATED function to create a sort column in the main table, and finally make the country column sort by the sort column.

refer:

https://community.powerbi.com/t5/Desktop/sort-months-into-chronological-order/m-p/2007639#M755993

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PVS07]

Thanks for the link. This is what I was looking for.

[mahoneypat]

Please see this article with a good way to do this.

Filtering the top products alongside the other products in Power BI - SQLBI

Pat