Conditional Colors in Bar Chart Using Dimension Values

[ss_s8]

Hi,

I’ve just started using Looker and I created a histogram of two data series that are blended together. So I have 2 dimensions, “grouping” and “monetization”, and 1 measure, “percentage”. So a sample of the data would look like this:
Grouping Monetization Percentage
A $1.00 80%
B $1.00 75%
A $2.00 20%
B $2.00 25%

I’m currently displaying the data in a bar chart, and i have it ordered in the way that I want with the percentage values showing - so it’s mostly good. The issue I have is that I want to visually differentiate the two groupings, so I’d like to use the chart options to define a different color for the two groups. I was thinking green for grouping A, and blue for grouping B. Is this possible? I know I can do this in the LookML for the values, but I don’t know how to do this within a visualization, or if it’s even possible. Any help would be greatly appreciated!

[ss_s8]

Sorry if this is a duplicate, I am new to this and couldn’t find anything that sounded similar.

[Morgan1]

I’d recommend pivoting your data by the ‘Grouping’ dimension. This will create one series for each grouping. You can learn more about pivoting in this document. Then you can control the color of each group using ‘Series Colors’ in the Style section of the visualizations.

Here’s something I whipped up to illustrate that. The dimensions that I chose are city and age, and the measure is count (percent of total).

This is what the chart looks like before pivoting:

And here’s what the chart looks like after pivoting:

It just so happens that our default colors are blue and green, but if you want to change it, you can use the legend text to populate the Series Colors section of the visualization settings.

Let me know if you have any questions about this!

[ss_s8]

That worked perfect, thank you!

[Morgan1]

Awesome! Sure thing!

[JakubD]

Hi,
Great post here. I wanted to ask what’s the best way to format the bar chart according to value. I’d like to display negative values in red and positive in green.

Any ideas? Thanks

[izzymiller]

Check out @brecht’s comment here:

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

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 tab…

The table calcs there ought to work great for this.

[JakubD]

Thanks a lot Izzy 🙂 It worked like magic!

[Arnaud]

Hi - coming back to this topic as it seems just not possible that looker does not provide this basic viz tool feature, however I’m not finding it!!!

So on a bar chart, we want values under 100 should be red, over 100 green (easy!). This is a time series, say, by month (so it cannot pareto’d / ordered like the example above).

How can we do that simply?

Thanks!

[David_P1]

Hi @Arnaud

 I think this thread should guide you in the right direction: 

https://community.looker.com/dashboards-looks-7/how-to-set-conditional-colors-in-chart-based-on-meas...

[Arnaud]

Hi @David_P1 and thanks!!

Yes, I saw these posts, but they do not fit the issue 🙂

I cannot order the set so that it distinguished 2 populations (and I do not want 2populations!)

Said otherwise, I need a bar chart like Jan, Feb, Mar, Apr, May, …. nd I cannot rearrange like Jan, Apr, Feb, Mar, May, …. for obvious reasons. or is this working without reordering ?

What is surprising is that any powerbi, tableau, spotfire or Qlik have this as base of base feature! So I’m sure there is something out there!!!!

[David_P1]

Hey @Arnaud,

I agree it would be ideal to have conditional formatting in column/bar charts in Looker (tables do, but unfortunately not charts).

You can always suggest new features:  https://portal.feedback.us.pendo.io/app

Having said that, the post above does cover your use case. You should not have to rearrange your x-axis (date dimension), but rather make use of table calculations.