Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 table even though it has a high count. How do I do this? 

Thanks in advance, highly appreciate it 🙂

DAX
fixed value
sort
sorting
Table
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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.

     

6 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        lukiz84
        Memorable Member
        3 years ago

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

         

        Then i think it's not possible.