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

gregono
Participant I

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!

12 26 13.1K
26 REPLIES 26

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

gregono
Participant I

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 🙂

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:

jkassof
Participant I

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 💯

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: 
    }
      measure: count_l_500 {
        type: number
        sql: CASE WHEN ${count} <= 500 THEN ${count} ELSE NULL END  ;;
        drill_fields: 
      }

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

      This approach will preserve drilling into the vis!

      Cheers,
      Adina

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.

JohnRomanski
Participant III

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.

Hi @adina_katz,

This is a good idea however I have always had issues with drilling into measures that use CASE WHEN. I usually have to revert to this:

If a measure is being drilled into and outputting incorrect information, this could be due to a CASE WHEN statement in your measure. Let’s say we want a measure that counts how many companies have exactly 13 employees. We could create a measure that includes a CASE WHEN statement in the sql parameter. For example,
New LookMLmeasure: count_employees_thirteen { type: count_distinct sql: CASE WHEN ${number_of_employees} = 13 THEN ${company_id} ELSE NULL END ;; drill_fields: [comp…

Can you confirm that drilling on this works because it is a number possible instead of an aggregation?

Thank you,
Drew

Hi There 🙂
Any new updates if this is possible now?

Yes, I keep on having a few dashboards pending release because I’m awaiting this feature and the color scheme is just too important!

For Looker users who have active accounts, I think you can also upvote this by clicking the Help icon in the upper right corner of your Looker instance, then clicking on Product Idea?

There they seem to use Pendo to manage feature requests. The issue # is 17646. If this link doesn’t work, you can click on any issue and just replace the # in the URL:

https://portal.feedback.us.pendo.io/app/#/case/17646

Hi, it’s February 2022.  Does this exist yet?

Hi Looker team,

this Option mentioned:

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

Is the best, most robust solution. 

This is a neat feature and would draw a TON of visual value and impact to a chart. Please please plase make this a reality

Hi Looker team,

this Option mentioned:

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

Is the best, most robust solution. 

This is a neat feature and would draw a TON of visual value and impact to a chart. Please please plase make this a reality

I support this 100%. 

2023, still nothing 😞

coming from 2023, still not here. Is Looker/Google actually caring about the user feedback and making improvements at all?

Coming August 2023 and the feature still not here 😞 We really want this feature.

I can do this formatting in Google sheets, hope they can just do the same thing in looker. its not that big deal i guess. hope some one can really look into this.

Yes, you can definitely set colors or apply custom formatting to your bar chart based on a measure's value in various data visualization tools and software, including Excel. This can help you emphasize certain data points or highlight specific trends in your chart. Here's a general guide on how to achieve this in Excel:

  1. Create Your Bar Chart: First, create your bar chart using the data and measures you want to visualize.

  2. Select Data Series: Click on the data series (bars) you want to format differently based on your measure's value. You can do this by clicking on a single bar to select all bars in the series.

  3. Conditional Formatting:

    • In Excel, conditional formatting allows you to change the formatting (e.g., colors) of cells, shapes, or data points based on specified criteria. To set colors based on a measure's value, you can use conditional formatting as follows:

      • Go to the "Home" tab in Excel.
      • Click on "Conditional Formatting" in the "Styles" group.
      • Choose "New Rule."
      • In the "New Formatting Rule" dialog box, select "Format cells that contain."
      • Set the conditions based on your measure's value. For example, you can specify conditions like "greater than 130" and "less than or equal to 130" to distinguish between values above and below 130.
      • Click on the "Format" button to define the formatting (e.g., change the font color or fill color).
      • After setting the formatting for each condition, click "OK" to apply the formatting rule.
  4. Apply the Formatting Rule: Once you've defined your formatting rules, Excel will apply the chosen formatting to the selected data series based on the conditions you specified.

  5. Fine-Tune Formatting: You can further fine-tune the formatting by adjusting colors, line styles, or other formatting options to achieve the desired visual effect.

  6. Add Reference Line: To add a reference line (e.g., the red line at 130 in your example), you can insert a line or shape object into your chart and position it at the desired value. Then, format it as needed.

Keep in mind that the exact steps may vary slightly depending on your version of Excel and the specific features available. Other data visualization tools like Tableau, Power BI, or Python libraries like Matplotlib and Seaborn offer similar capabilities for custom formatting based on data values. The key is to explore the conditional formatting options and apply them to your chart as per your requirements.

September, 2023

Any news about it?

It's sounds easy when you release that in PBI and Tableau is very simple...

Top Labels in this Space
Top Solution Authors