Forum Discussion
Dynamic table sorting based on total count with fixed value at the bottom
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...
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.
v-yadongf-msft
Community Support
Community SupportHi 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.
It worked, thank you so much for the help! 🙂