MLearning.ai
Published in

MLearning.ai

Take your Table visual colour-coding to the next level (including accessible alternative)

Conditional formatting for table visuals in Power BI is great. It allows us data analysts to quickly guide users to the most relevant numbers and gauge the performance at a glance.

For instance, a project’s Actual vs Budget expenses written in green probably means you’re spending less than expected; conversely, red means you’re spending too much. In other instances, you can even use icons to inform the user: ❌ for when you’re behind a deadline, ⚠ for when you’re close to hitting the deadline, and ✔ for completed/on track tasks.

Today I want to show you another alternative use of conditional formatting to let users know which data points they should pay more attention to.

Original table visual
Original table visual

In this example I will use a table of clients. The idea is to highlight the clients whose deals are have not closed yet, taking into account their priority and close date as so:

Table visual with colour-coding column
Table visual with colour-coding column

Instead of using the techniques mentioned before, such as the text formatting or using icons, I added a thin coloured column at the start to colour code clients. Gray clients have been closed, red are high priority, orange medium, and green low. Moreover, notice how this thin column removes the need to keep the Priority column, hence shortening the visual’s width.

This was achieved with two measures and conditional formatting on the new column. The first measure is a placeholder to show a value in the column:

Placeholder Column returns zero for all data points. The idea is to have any data for all data points, but to occupy as little space as possible so the column can be thin without increasing the row height due to word wrapping.

The second measure is the one that chooses the colour based on two rules: the client priority, and whether the client has been closed, i.e. it has a close date.

First I defined three variables to facilitate the conditions in the switch case, based on the priority and the close date. Then the measure will return the hexadecimal colour strings: gray for closed clients, green for low priority, orange for medium, and red for high. The idea is that gray will catch less of the user’s attention because it belongs to closed clients, and so they can split their attention based on the priority of the open clients.

So with these two measures in place, I added Placeholder Column to the table visual and removed Priority. At the moment the column simply shows zeroes, and there is a rogue row with blanks.

Placeholder column result
Placeholder column result

First thing is to add a visual-level filter to not show blank Clients and rename the Placeholder Column column to just a dot (.).

Remove rows without Client name
Remove rows without Client name
Double click on Placeholder Column and rename it to a dot
Double click on Placeholder Column and rename it to a dot

Oh, and to completely “hide” the name of that new column, use the field formatting options. The field formatting will be overwritten by the conditional formatting, but it won’t affect the header.

“Hide” column header
“Hide” column header

Now that the to-be-coloured column is in place, it is missing the conditional formatting rules.

Conditional formatting for the coloured column
Conditional formatting for the coloured column

And inside the advanced controls

Conditional formatting rules for background colour
Conditional formatting rules for background colour

The rule is to use the Client Cond Format measure I showed before. The measure itself resolves which colour should be used as the background colour.

And don’t forget to repeat this rule for the font colour so the zeroes of the Placeholder Column measure use the same colour as the background for the perfect camouflage.

Table visual with colour-coding column
Finalised table visual with coloured column

You can even add a calculated column to use in a slicer so the user can hide the closed clients, i.e. the gray rows.

Slicer to hide closed clients
Slicer to hide closed clients

Now Dwight knows he should prioritise Client A (if he didn’t know already based on the their value!).

And that’s it for the first version. The only issue with this colour-coding column is accessibility, rather, the lack thereof. The different colours will mean nothing to colour-blind users. This could be attenuated with shapes instead of colours.

In the case of using icons, you can rewrite the measure to return a number instead of a colour. Then, edit the conditional formatting rules to use a different icon based on the number returned.

And now the conditional formatting rules would look like this:

Conditional formatting rules for icons (1)
Conditional formatting rules for icons (1)
Conditional formatting rules for icons (2)
Conditional formatting rules for icons (2)

Note: default icon sets in Power BI may not be the best for this specific scenario :( Give this thread a look if you want to use custom icons.

And the revised visual:

Revised table visual
Revised table visual

Instead of colours, priority is denoted by the number of filled bars in the icon, where a fully filled icon is a closed client. This way even if a user can’t distinguish colours they can still distinguish the kind of client they are looking at based on the shape of the icon!

Finally, here are the three tables for direct comparison.

Comparison of table visuals
Comparison of table visuals

P.S. if you’re looking for the .pbix file to play around with the tables, they are available on my GitHub repository.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
José Fernando Costa

I write about data science to help other people who might come across the same problems