Is it possible to define a color rule for data point 1 based on its value relative to data point 2?

bjavery87 · 2023-02-24T17:17:40+00:00

There was an error rendering this rich post.

I have 2 different lines with symbols on a graph. One line represents the target value and the other line represents the actual value. I want the symbol on the actual value line to be red when above the target value and blue when below the target value. I understand that I can change colors based on whether the actual value is above or below a manually entered value, but the target value changes every month, and I need the color of the actual value to automatically change when it dips below or rises above the dynamic target value.

Find more posts tagged with

Accepted answers

All comments

ArborRose

I would look into creating a custom column {formula} with the condition you need. The resulting column can then be used as the color condition. Thus, the new column would show when it dips below or rises above, and the color settings would react to that.

McSQL

@bjavery87 How I have done this in the past is almost identical to the suggestion from ArborRose, but here is more detail. I created two beast modes.

beast mode 1:case when value>=(target value) then value else 0 end

beast mode 2: case when value<(target value) then value else 0 end

use a line stacked bar chart. set color of beast mode 1 to red, beast mode 2 to blue. The chart will stack the red and blue but you will only see one or the other because the result of one beast mode should always be 0.