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!
-
Scott, thanks for the useful post! Several images in the post are showing as broken/missing (see https://screencast.com/t/GWIzHZ987UD). Would you mind checking them?
I'm also wondering if something similar can be done with the Table chart-type, i.e. conditional-formatting such that if a cell value is over a particular value, the background of that cell changes. -
Gary, Thanks for letting me know about the broken images. I'm not sure what happened, but I added them back in.
Right now we don't have any way to add conditional formatting to a table chart like you're describing, but I can pass your feedback to our product team. I did add this other tip about adding emojis to a table chart that could be helpful.
-
We had a use case to show pre and post-campaign and were able to show the effect of the campaign illustrated by changing the line color at the campaign start date. Here is what it looked like and the SQL code used to accomplish it.
SELECT day,
-- Pre-CampaignCASEWhen day <= '2019-11-08'::DATE THEN viewsend as "Pre-Campaign",-- Post-Campaign Start DateCASEWhen day >= '2019-11-08'::DATE THEN viewsend as "Post-Campaign Start Date"FROM report;The first CASE STATEMENT above takes all values in the column "views" that are LESS than or EQUAL to the campaign start date '201-11-08' and puts them into a column named "Pre-Campaign".
The second CASE STATEMENT above takes all values in the column "views" that are GREATER than or EQUAL to the campaign start date '201-11-08' and puts them into a column named "Post-Campaign Start Date".
You then build your chart and select both new columns as your y-axis and select the date column ("day" in this example) as your x-axis. And Tadaa! You have an amazing chart showing the impact the campaign is taking on overall views.
Please sign in to leave a comment.
Comments
3 comments