Adding Ranking Visual to a Matrix

[AndyMeiks]

Hi team,

I have the attached example screenshot for statistics I'm working with - with Team names the hidden values.

I was wondering if I can add conditional formatting to rank each of these columns, i.e. highlight a cell green when the team has ranked #1 in that stat category and red when they're ranked #4.

Is this possible? I'm also open to adding a ranking column but wanting the data to stay looking clean.

Thanks so much.

[Ritaf1983 · Accepted solution]

Hi @AndyMeiks 
You can format your matrix without adding unnecessary columns.

Please follow these steps:

1. Create the measure for ranking teams in every category :

Team Rank =

RANKX(

    ALL('Table'[Team]),

    CALCULATE(

        SUM('Table'[Value]),

        ALLEXCEPT('Table', 'Table'[Catrgory], 'Table'[Team])

    )

)

2. Apply conditional formatting based on this measure :

According to global accessibility law, it is forbidden to use traffic light colors to create emphasis because color-blind people cannot see these differences.
My recommendation or emphasis is only on red or the combination of red with blue.

Link to the sample file 

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

[AndyMeiks]

Hi there,

Thanks for this - when I input it I get an error saying Can't display the visual, and the error message:

MdxScript(Model) (7, 13) Calculation error in measure 'Table'[Team Rank]: The function SUM cannot work with values of type String.

Any ideas on how I could find and fix this?

Thanks.

[Ritaf1983]

Hi again @AndyMeiks 

It says you trying to do calculations on the string column instead of a number...
Check your data types and modify them.

[AndyMeiks]

Thanks, I have fixed this and marked as accepted solution! The only question I have on this is would it be easy to adjust this to reverse the ranks in certain rows?

For example, in my table I have both Goals (ranked 1-4) and Missed Shots (ideally should have the lowest number ranked as #1). Is this possible or simpler to break into 2 different matrix?

[Ritaf1983]

It wasn't clear what you were trying to accomplish.
It might be easier if you attach a picture.

However, for effective data visualization, one graph per message is most effective.
Maybe it is worth separating so that the user does not have to decode multiple messages from the same graph regardless of technical possibilities.