Sorting a Matrix by Color

[bernate]

Hello all! I have a matrix (see below) that is conditionally formatted by a calculated column named "Color." There are 4 colors- red, yellow, green, and blue. I would like to click on a month (column) in the matrix and have the values automatically sort in the order red, yellow, green, blue. If automatic sort is not possible, is there a way to just sort the whole table where each column is sorted by color? 

I have already created a Sort table and connected it to my Data table on the "Color" column. 

[v-xingshen-msft]

Hi @bernate,
What I tried was to use measure for sorting, if your dataset is huge you can use measure for sorting as measure takes resources only when it is used.

Sort Table(measure) = 
 SWITCH(
    TRUE(),
   MAX( 'Table'[Column])="Red",1,
    MAX('Table'[Column])="Yellow",2,
    MAX('Table'[Column])="Green",3,
    MAX('Table'[Column])="White",4,
   MAX( 'Table'[Column])="Gray",5,
    BLANK()
 )

It is also possible that my dataset is too simple to fully reproduce your problem, if you can provide your example data or pbix file, it will help you solve the problem faster, I look forward to your reply, I will get back to you as soon as I receive your message!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[bernate · Accepted solution]

Hi Tom Shen, thank you for your suggestion. I gave up on the matrix for my purposes and instead changed the visual to a table. 

This allowed me to add a Rank measure that helped me to sort the Sum of Balance. (The Combined Rank Color Measure is in the table and sorted DESC, but minimized)

Combined Rank Color =

VAR DateRank =

    RANKX (

        ALL ('Table'[Date]),

        CALCULATE ( SELECTEDVALUE ('Table'[Date]) ),

        ,

        DESC

    )

VAR ColorRank =

    RANKX ( ALL ('Table'[Color]),[Sum Balance])

VAR ColorCount =

    CALCULATE ( DISTINCTCOUNT ('Table'[Color]
]), ALL ('Table') )

VAR HiddenFieldRank =

    RANKX ( ALL ('Table'[HiddenField]),[Sum Balance])

VAR HiddenFieldCount =

    CALCULATE ( DISTINCTCOUNT ('Table'[HiddenField]), ALL ('Table') )

VAR CombinedRank = DateRank

     + ColorRank + HiddenFieldRank

    / (  ColorCount + HiddenFieldCount + 1 )

RETURN

    CombinedRank

[v-xingshen-msft]

Hi @bernate ,

Thanks for the feedback, it's great to see that you've found a solution! If you have any other questions or need From the current situation, you have found the corresponding solution situation, if you can, please mark your answer as solved to help other users encountering similar problems, if you have further questions, feel free to contact me, I will reply to you as soon as I receive the message!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[dharmendars007]

Hello @bernate , 

Please make sure you have sorted the "Color" column in your main data table by "Sort Order" column.

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[bernate]

Hi Dharmendar, yes I do. Here is the result when I apply a sort to the matrix.

As you can see, the sorting doesn't seem to be in any order.