# color on bar graph based on range

Hi All, Is it possible to change the color of  single bar in a bar chart based on its value so if the 'Sales' (in bar chart) <500k then color the bar red, > 500  then green.

I would really appreciate any help with this. Thank You

• ###### 1. Re: color on bar graph based on range

In the future, please remember to post a sample workbook. It's always easier to help with some data to show.

There are a number of ways to do this:

1) Calculated Field with Static Number:

Create a calculated field:
IF SUM([Sales]) < 500000
THEN "Red"
ELSEIF SUM([Sales]) >= 500000
THEN "Green"
END

Drag this into the color shelf. Edit the colors so that the color that is supposed to be red is red, and the color that is supposed to be green is green.

2) Change Color Pallete with static number:

Drag sales to the color shelf. Edit the colors. Select the color pallete you want (red-green diverging). Check Stepped color and change the number of steps to 2. Click the advanced drop down. Check the Center box. Edit the center number to be 500000.

3) Calculated Field with a threshold (so you can change the dollar amount easily and the colors will update based on what is above or below the new number):

Create a parameter and name it Threshold (or really whatever you want).
Choose the data type (probably integer).
In allowable values select Range and then select the minimum value of the threshold, the maximum value of the threshold, and how large you want the step size to be. Step size is by how much you want the current value to increase everytime you move the parameter control. This is how I like to do it and the minimum and maximum are based on the domain knowledge of the dataset and any future values that could come in. You could just leave this as All, I suppose.

Create a calculated field similar to #1 above, but this time place the threshold in as the value.

IF SUM([Sales]) < [Threshold]

THEN "Red"

ELSEIF SUM([Sales]) <= [Threshold]

THEN "GREEN"

END

Right click the Threshold parameter and select Show Parameter Control. Drag the calculated field into the color shelf. Edit the colors so that the color that is supposed to be red is red, and the color that is supposed to be green is green. Now, when you play with the parameter control, your chart should update to show different colors based on if the sales of that row is greater or less than the threshold.

If you do options 1 or 2 and you want to change the value, you'll have to change it in the calculated field or in the color pallette controls, respectively. That is why I like #3.

Hope it helps. And remember, post workbooks!