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

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

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

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.

@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:

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

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

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

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:

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

What’s the update on time-based conditional formatting?
I want to highlight today’s measures of things without messing with filters.

More advanced formatting options are coming with the final phase of the new table visualization that’s been rolling out in chunks as the beta table-next visualization option. I’m not sure if time-based formatting will be included, but row-by-row custom formatting will definitely be possible then so you’d be able to easily highlight today.

1 Like

Is this possible today (Aug 2019)?

No, it’s not. I also was a bit off base with my above comment, which was targeted at the Table Visualization rather than bar/column viz.

As far as I know, we don’t have plans currently to implement conditional formatting on other viz types besides Table.