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

[Anonymous]

Hi,

I want to sort this table dynamically. So that the color category with the highest count is at the top, and the lowest at the bottom, but, I want "Unknown", to always be the last value in the table even though it has a high count. How do I do this? 

Thanks in advance, highly appreciate it 🙂

[v-yadongf-msft · Accepted solution]

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.

[Anonymous]

Hi again @v-yadongf-msft,

I have now got 0 values as well (For example for Color: Pink, the Sum of Count = 0). How can I make it work with that?

Thanks in advance, highly appreciate it!

[Anonymous]

It worked, thank you so much for the help! 🙂

[lukiz84]

Count is a measure? 

[Anonymous]

No, it just an automatic count done by the table of the IDs for each category. Here is the table the data is taken from. So, no measure is used so far.

[lukiz84]

Ok, "Auto count" is a measure too (an implicit measure, not adviced, but ok) 🙂

Then i think it's not possible.