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

[tomaszkarczewski]

@dawiddebowski your solution works perfect when we have one bar to show and color based on another column. I have a slightly more complex situation, where I want to have one bar only in gray, and then color another bar based on yet another, third column. The option suggested by you color all the bars in the bar chart and it can’t be overridden with field overrides.

Is there some other way to override the color of one bar to gray, or maybe yet completely another solution to make it work?

[vascodorey]

Thank you all.

What I ended up doing was a small python script that calculates the gradient and outputs the JSON. I then go to Inspect → Panel JSON and paste the following.

      "mappings": [
        {
          "options": {
            "from": -1,
            "result": {
              "color": "#f2495c",
              "index": 0
            },
            "to": -0.02
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.02,
            "result": {
              "color": "#f2495c",
              "index": 1
            },
            "to": -0.019
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.019,
            "result": {
              "color": "#f25264",
              "index": 2
            },
            "to": -0.018
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.018,
            "result": {
              "color": "#f35b6c",
              "index": 3
            },
            "to": -0.017
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.017,
            "result": {
              "color": "#f36474",
              "index": 4
            },
            "to": -0.016
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.016,
            "result": {
              "color": "#f46d7c",
              "index": 5
            },
            "to": -0.015
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.015,
            "result": {
              "color": "#f57684",
              "index": 6
            },
            "to": -0.014
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.014,
            "result": {
              "color": "#f57f8c",
              "index": 7
            },
            "to": -0.013
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.013,
            "result": {
              "color": "#f68895",
              "index": 8
            },
            "to": -0.012
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.012,
            "result": {
              "color": "#f7919d",
              "index": 9
            },
            "to": -0.011
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.011,
            "result": {
              "color": "#f79aa5",
              "index": 10
            },
            "to": -0.01
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.01,
            "result": {
              "color": "#f8a4ad",
              "index": 11
            },
            "to": -0.009
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.009,
            "result": {
              "color": "#f9adb5",
              "index": 12
            },
            "to": -0.008
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.008,
            "result": {
              "color": "#f9b6bd",
              "index": 13
            },
            "to": -0.007
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.007,
            "result": {
              "color": "#fabfc5",
              "index": 14
            },
            "to": -0.006
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.006,
            "result": {
              "color": "#fbc8ce",
              "index": 15
            },
            "to": -0.005
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.005,
            "result": {
              "color": "#fbd1d6",
              "index": 16
            },
            "to": -0.004
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.004,
            "result": {
              "color": "#fcdade",
              "index": 17
            },
            "to": -0.003
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.003,
            "result": {
              "color": "#fde3e6",
              "index": 18
            },
            "to": -0.002
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.002,
            "result": {
              "color": "#fdecee",
              "index": 19
            },
            "to": -0.001
          },
          "type": "range"
        },
        {
          "options": {
            "from": -0.001,
            "result": {
              "color": "#fef5f6",
              "index": 20
            },
            "to": 0
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0,
            "result": {
              "color": "#ffffff",
              "index": 21
            },
            "to": 0.001
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.001,
            "result": {
              "color": "#f8fbf7",
              "index": 22
            },
            "to": 0.002
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.002,
            "result": {
              "color": "#f1f8f0",
              "index": 23
            },
            "to": 0.003
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.003,
            "result": {
              "color": "#eaf5e8",
              "index": 24
            },
            "to": 0.004
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.004,
            "result": {
              "color": "#e3f2e1",
              "index": 25
            },
            "to": 0.005
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.005,
            "result": {
              "color": "#dcefd9",
              "index": 26
            },
            "to": 0.006
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.006,
            "result": {
              "color": "#d5ebd2",
              "index": 27
            },
            "to": 0.007
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.007,
            "result": {
              "color": "#cee8ca",
              "index": 28
            },
            "to": 0.008
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.008,
            "result": {
              "color": "#c7e5c3",
              "index": 29
            },
            "to": 0.009
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.009,
            "result": {
              "color": "#c0e2bb",
              "index": 30
            },
            "to": 0.01
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.01,
            "result": {
              "color": "#b9dfb4",
              "index": 31
            },
            "to": 0.011
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.011,
            "result": {
              "color": "#b2dbac",
              "index": 32
            },
            "to": 0.012
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.012,
            "result": {
              "color": "#abd8a5",
              "index": 33
            },
            "to": 0.013
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.013,
            "result": {
              "color": "#a4d59d",
              "index": 34
            },
            "to": 0.014
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.014,
            "result": {
              "color": "#9dd296",
              "index": 35
            },
            "to": 0.015
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.015,
            "result": {
              "color": "#96cf8e",
              "index": 36
            },
            "to": 0.016
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.016,
            "result": {
              "color": "#8fcb87",
              "index": 37
            },
            "to": 0.017
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.017,
            "result": {
              "color": "#88c87f",
              "index": 38
            },
            "to": 0.018
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.018,
            "result": {
              "color": "#81c578",
              "index": 39
            },
            "to": 0.019
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.019,
            "result": {
              "color": "#7ac270",
              "index": 40
            },
            "to": 0.02
          },
          "type": "range"
        },
        {
          "options": {
            "from": 0.02,
            "result": {
              "color": "#73bf69",
              "index": 41
            },
            "to": 10000
          },
          "type": "range"
        }
      ]

Thank you all for your help!

[dawiddebowski]

I think it would be best to create a new topic. Please, include the data (possibly in tabular format - your data and toggle the Table View if possible) and the desired result. You can ping me there (or we can continue here but I’m not sure if I will get the notifications )