Dynamic coloring for barchart bars by text value

[samdataguyiam]

• What Grafana version and what operating system are you using?: Grafana v11.5.1, Windows

• I want to create a barchart for this situation. I have a table with columns A (unique text label value), B (text value that is not unique but is a category that A falls in), and V (numeric value). I want a barchart where each bar corresponds to a value of A and the height is V. I would like the bars colored by the value of B. But the key is I want the coloring to be dynamic according to the existing values of B in the table; the mapping is not important but just that it be dynamic without having to pre-specify mappings and thresholds. Here A and B are the first two columns and V is the third.
  

  

• I select “Bar Chart” and In “X-Axis” I select the column A. This results in the correct chart. However, I want to then color by B. In “color by field” I select column B. Instead of doing what it implies, it colors all bars the same. I try to add an override for column B → standard options → color scheme. Flipping through the options changes the resulting color but the single color is maintained. For instance “Red-Yellow-Green (by value)” makes all the bars red. One thing I could do is create a unique integer value column for each value of B by DENSE_RANK() OVER (ORDER BY perturbation_method_category) AS category_integer. I could then subtract 1 and then divide by the maximum to map these to 0-1 but this doesn’t seem to help. It actually seems to hurt because then this is an additional numeric column and the bar chart wants to display it as well (I only want to use it to color, not to draw bars to its height).

• What happened?: the bars changed color but stayed the same color.
  

  

• What did you expect to happen? I expected the color of the bars to change dynamically by the value of the text column I chose.

[infofcc3]

Hello @samdataguyiam using value mapping and threshold apply on v in bar chart but you want apply based on column b values you can try this …

you can add one extra column like category_code using query and give random number based on values like case → 1 , direction → 2 and so on …then hide column not seen in char you can use this using override.

SELECT 
  perturbation_method,
  perturbation_method_category,
  any_checker,
  CASE 
    WHEN perturbation_method_category = 'case' THEN 1
    WHEN perturbation_method_category = 'distraction' THEN 2
    ELSE 0
  END AS category_code
FROM perturbation_data;

Final output:

[samdataguyiam]

Thank you, but this is not truly dynamic because you have to set the thresholds anyhow for a set of integers, as long as you can specify enough. Ideally I guess would be to automatically map the integer conversion to floats between 0 and 1 and then apply a gradient which would do the same thing.

[samdataguyiam]

Ok I was able to find a solution that works decently enough.

• In the SQL query, create this column which codes the column B we want to color by as integers 0 … (number_categories - 1):
  ((DENSE_RANK() OVER (ORDER BY perturbation_method_category)) -1) AS category_integer
• In a new select statement after this, normalize the category_integer column to unique values 0 – 100 by min-max scaling * 100 (you could also do this in a single step above):
  (category_integer * 100.0) / (max(category_integer) over()) as category_float
  The multiplication by 100 is needed because apparently the default color scheme map range is 0 - 100. The specification of float value 100.0 makes it a float rather than an integer.
• In Grafana, create a bar chart from this query, with the following options selected:
• X-axis: perturbation_method (A), the (unique) row labels
• Color by field: category_float (the transformed value of perturbation_category, column B)
• Standard Options: color scheme → Red-Yellow-Green (by value); you can also choose a different spectrum palette, but it is better to choose one that has a wider color variety.
• Now, because we have multiple numeric columns, the default behavior of the bar chart is to plot all of them for each value of the X-axis (A). Therefore we have to hide them, as follows: For each of the columns category_integer, category_float, do “Add Override” → Fields with name - > select the name → Series → Hide in Area → click on “Viz” (visualization). This produces the following table and plot:
  
  
  
  
  

[infofcc3]

Great…Thanks for your explanation.