Change the Color in a Chart When the Number Hits a Goal

Charts make it easy to get valuable information at a glance, and here’s a tip to make it easier to see if a number has surpassed a specific goal or quota.

You could always use the goal line to show how close the data is to that goal, but I want to show you how you can change the color of the bar once it hits that goal, as seen below:

Note: You will need to use SQL, so you will have to customize this to match how your data is set up.

I prepared the data by adding a filter and a group transform so I had my total sales by rep for the month. You can do some of that in SQL along with this next step if you’d like or use a transform, but you basically need to end up with the totals to compare with your goal.

Next you will add CASE statements to show whether the total is above or below the goal. This will divide the numbers into two columns, one for above goal and one for below goal. You will want to make sure that one of the CASE statements includes an equal sign in case the number is right on the goal.

SELECT "Sales Rep",
CASE WHEN "Sum of MRR" >= 8000 THEN "Sum of MRR" END AS "Over Goal",
CASE WHEN "Sum of MRR" < 8000 THEN "Sum of MRR" END AS "Under Goal"
FROM report;

Now you have the sales numbers in either a “Over Goal” column or a “Under Goal” column. The next step is simply to chart it. You need to use one of the stacked chart options, either as a column or bar chart.

Select both of the sales columns as a value series, and then select your chart labels. In this example, I changed the colors of each value series to make them more distinct, and sorted the sales rep by name so they will always appear in the same order.

If your goals change, you will need to edit the SQL in each CASE statement to compare against the new number.

Including Multiple Categories in the Chart

You can also break down the total into different categories to show what made up that total. For example, if I want to see the total sales broken down into new sales and upgrades, I can add a couple more CASE statements. First I will need to also group the data by those categories as well. (So in my example, group it by sales rep and by sales type.)

Here’s an example of the SQL you can use. I still want to compare the overall total against the overall sales goal, so in the CASE statement I will add the two types of sales together and see if it is higher or lower than the goal, and assign a column for each sales type.

SELECT "Sales Rep",
CASE WHEN "New"+"Upgrade" > 3000 THEN "New" END AS "New Deals over goal",
CASE WHEN "New"+"Upgrade" > 3000 THEN "Upgrade" END AS "Upgrades over goal",
CASE WHEN "New"+"Upgrade" < 3000 THEN "New" END AS "New Deals under goal",
CASE WHEN "New"+"Upgrade" < 3000 THEN "Upgrade" END AS "Upgrades under goal"
FROM report;

This will give me four columns (plus the sales rep), and you will notice that the “New” and “Upgrade” totals each end up in their own columns, but they are labeled as over or under goal.

Now we just chart it the same way by selecting the four columns as value series and format them how we want.

Have you found something like this that works for you? Share it as a new post or comment with some details!