Custom bar chart coloumn based on reference line

[#1]

Hi Community,

I would like to ask if this is possible in bar chart.

My chart is like this:

I want to change the color of the part of the bar that exceeds the reference line like this:

Is this possible? Any idea or help is much appreciated.

Thanks!

Nadsky

[celindho · Accepted solution]

Hi!

As far as I know, this cannot be done directly but there are workarounds.

The simplest would be to make a stacked bar chart:

Assuming your expression in the chart is sum(Allocation) and your reference line is set at referenceValue, you can create two expressions, one for the blue part and one for the red part.

First expression: min( sum(Allocation), referenceValue) - The "min" prevents the bar from growing over the reference line

Second expression: max(sum(Allocation) - referenceValue, 0) - If the original expression value is less or equal to the reference line, we get a value of 0, otherwise we get the part that exceeds the reference line

By coloring the first blue and the second red, you get the desired effect. I think my solution would not work with negative or mixed sign values, but shouldn't be too hard to modify for that case also.

Hope this helps!

-Christian

[#3]

Hi Christian,

Thanks for the idea of using stack bar chart. The min max function seem not working on my expression (allocation is a formula sum(field1)/sum(field2) ). I used if-else condition instead.

Thanks for helping me out! 😄

[celindho]

Glad that my suggestion got you on the right track.

Did you try to put the sum(filed1)/sum(field2) part into the min or max functions?

[celindho]

Now i realized that you actually have to use the rangemin and rangemax functions instead of min and max. That way you get it working without the if statements.