Grafana bar chart - hide one column, but color bar according to its value

[vascodorey]

• What Grafana version and what operating system are you using?
  Grafana on docker (Grafana v11.5.2 (598e0338d5))

• What are you trying to achieve?
  I have a finance query that returns two columns (“€ change” and “% change”). I want to display a bar chart that:

1. Displays “€ change” in bars
2. Colors the each column according to “% change”, e.g.:

• If less than -1% - bright red
• If between -1% and 0% - not so bright red
• If between 0% and 1% - not so bright green
• If more than 1% - bright green

3. Hides “% change”
4. Nice to have - when I hover over the bar, it would display % change

• How are you trying to achieve it?
  I do not know how do to this - community, can you help?

• What happened?
  Not applicable

• What did you expect to happen?
  Not applicable

• Can you copy/paste the configuration(s) that you are having problems with?
  Here is my query:

SELECT 
  o.Portfolio,
  SUM(o.`Position` * d.`today_price`) - SUM(o.`Position` * d.`yesterday_price`) AS ` € Change`,
  (SUM(o.`Position` * d.`today_price`) - SUM(o.`Position` * d.`yesterday_price`)) / SUM(o.`Position` * d.`yesterday_price`) AS `% Change`
FROM day_change d 
INNER JOIN ticker_defs t ON t.`Yahoo Ticker` = d.`ticker`
INNER JOIN openpositions o ON o.`Yahoo Ticker` = t.`Yahoo Ticker`
GROUP BY o.Portfolio;

• Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
  Not applicable

• Did you follow any online instructions? If so, what is the URL?
  Not applicable

[dawiddebowski]

Hi,
Assuming your data looks like this (radom in CSV, would create a table like this in Grafana):

portfolio,euro,percent
A,1,0.01
B,2,0.02
C,-1,-0.01

You can do the following:

1. Under Bar Chart options find Color by field option and set it to percent (or % Change in your case)
   

   

2. Scroll down to the end of the visualization settings, find Field overrides. Add field override for Fields with name, pick the % Change name (or something that would be similar - I’m not sure how the names work in SQL datasource). In there add two overrides:

• Series > Hide in area toggle Viz and Legend
• Value mappings - create value mappings of type range for your selected ranges of values and set the desired colors.
  
  

Should yield a result like this

[vascodorey]

Outstanding, this works!

Additional question - is there a way to display a color gradient instead of having several thresholds? For instance:

1. 0 White (255,255,255)
2. 100% Green (0,255,0)
3. -100% Red (255,0,0)

If I am getting +50%, it would be (0, 128, 0)
If I am getting -50%, it would display (128, 0, 0)

Thanks!

[dawiddebowski]

Unfortunately, not that I’m aware of and not automatically. You could ofc do that by adding more ranges by hand but I guess it’s far from perfect. There’s built-in color scheme, but it’s not with white, but with yellow, so I guess it wouldn’t suit your needs (if you want to try it though, you’d need to remove the value mappings and select this color scheme and optionally toggle Field min/max option)?

[vascodorey]

Thanks! I’ll check it out, otherwise I will create 8 or 10 thresholds to achieve my goal.

Cheers