Dynamic table sorting based on total count with fixed value at the bottom

Hi Anonymous ,

This is my test table:

Please create two columns:

Count = IF('Table'[ID] = MINX(FILTER('Table','Table'[Category] = EARLIER('Table'[Category])),'Table'[ID]), CALCULATE(COUNT('Table'[Category]),FILTER('Table','Table'[Category] = EARLIER('Table'[Category]))))

Rank = SWITCH(
    TRUE(),
   'Table'[Category] = "Unknown" && 'Table'[Count] <> BLANK(),DISTINCTCOUNT('Table'[Category]),
    'Table'[Count] <> BLANK(),RANKX('Table',[Count],,DESC,Dense))

You can sort by [Rank]:

If you don't want to see Rank column in table visual, you can hide it in Specific column:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.