How to set conditional colors in chart based on measure value?

bar
visualizations
(Greg N) #1

Can I set colors (or apply other custom formatting) in my bar chart based on a measure’s value?

Here is a simple example generated in Excel. There is a red reference line at 130. All the measure values greater than it are dark green and the bars below it are a lighter green.

I like how I can use the “html” parameter to apply conditional formatting to measures. But can I do the same for these measures in the visualization?

Thanks!

5 Likes

(LC) #2

This is exactly what I am looking for, any response to this or did you figure it out?

0 Likes

(Greg N) #3

I haven’t heard anything from Looker.

I noticed that release 4.8 has conditional formatting for table visualizations, so perhaps it will be coming for other visualization types in the near future.

0 Likes

(Max Corbin) #4

@gregono and @lchang No word yet on the timeline for being able to do conditional formatting on other kinds of visualization, but this is the direction we’re heading :slight_smile:

0 Likes

(Dennis Tai) #5

This or the option of color gradient based on value would be nice too.

0 Likes

(Elliot Park) #6

Hey Dennis,

Thanks for the feedback. I’ll let the product team know that you’d like to see conditional formatting options for bar chart visualizations in the future!

Best,
Elliot

0 Likes

(Brecht Vermeire) #7

You could potentially use the pattern below, if you don’t have too many buckets.

Imagine I’m looking at user count per state and I want to break out states with 300+ users. Select the dimension & measure and run the query.

Now, create two (or more) table calculations:

if(${users.count} >= 300, ${users.count}, null)

and:

if(${users.count} < 300, ${users.count}, null)

Hide the original measure, and go into viz settings and stack them. This will remove the extra whitespace between the two table calc series. Add a trend line if you like.

The end result should look like this:

2 Likes

Conditional Colors in Bar Chart Using Dimension Values
(Jordan K) #8

Unfortunately the table calculation method makes drills unusable, some native way of doing this so that we don’t lose the drilling functionality would be :100:

0 Likes

(Adina Katz) #9

Hey @jkassof ,

We could do what Brecht has suggested using measures instead of tablecalcs- then we can define drills in those measures.

We’ll want to create two measures with CASE WHENs in the sql parameter, like so:

measure: count_g_500 {
    type: number
    sql: CASE WHEN ${count} > 500 THEN ${count} ELSE NULL END;;
    drill_fields: [list of fields to drill into]
  }
  measure: count_l_500 {
    type: number
    sql: CASE WHEN ${count} <= 500 THEN ${count} ELSE NULL END  ;;
    drill_fields: [list of fields to drill into]
  }

Here’s an example of what this looks like in the explore:

This approach will preserve drilling into the vis!

Cheers,
Adina

1 Like

Conditional Formatted Single Dimension Column Chart