As close as you can get to a Bullet Graph with Looker's out of the box charts (Actual vs Target)

[Cyril_MTL_Analy]

Hello there Looker community!

I’ve been building bullet-like graphs for quite a lot of clients so I thought I’d share the knowledge here! The goal is to be able to display an actual number versus a target and a pace. It usually is used with revenue/sales numbers.

This is the end goal:

Prerequisite

• An actual number for a given month which should be a measure that will be defined as ${target} in this tutorial
• A target number for a given month that can be built in a measure or a table calculation ${sales} in this tutorial

Modeling the data

You are going to build 5 Table Calculations:

1. Expected Pace
   
   

code for copy:

(${target}/extract_days(add_days(-1, date(extract_years(add_months(1, now())), extract_months(add_months(1, now())), 1))))*extract_days(now())

This gives us the expected revenue/sales where we should be at for the current day of the current month in a $ amount).

That field won’t be used in our visualization.

2. % of Target

(excuse the red dot, for the sake of the example, I’ve renamed the sales measure)

code for copy:

${sales}/${target}

This gives us the current percentage of completion towards our target (percentage)

That field will be used in our visualization.

3. Pace

There we’ll start referencing the above calculations

(excuse the red dot, for the sake of the example, I’ve renamed the sales measure)

code for copy:

(${sales}-${expected_pace})/${expected_pace}

This gives us a the percentage value of where we stand compared to the Pace. If that value is negative we’re behind, if the value is positive then we’re ahead!

That field won’t be used in our visualization.

4. Expected Pace Percentage

code for copy:

if(((${expected_pace}/${target})-${of_target}) > 0, ((${expected_pace}/${target})-${of_target}), null)

This gives us the percentage needed to match the pace compared the actual sales or a null value if we’re ahead of the pace.

That field will be used in our visualization.

5. Remaining

And finally:

code for copy:

if(${pace}<0, 1-(${expected_pace_percentage}+${of_target}),if(1-${of_target}<0,null,1-${of_target}))

This gives us the percentage remaining to match the target based on the pace (if our sales are behind it) or the sales (if they are ahead of the pace).

That field will be used in our visualization.

---

Once everything is set you should have something like that:

Building the visualization

Now that we have our data, let’s start visualizing it!

If you select the bar chart, you should have something that will look like this:

That is obviously not good, so let’s start hiding series.

-> In the data panel, hide all the fields but those three Table Calculations:

• % of Target
• Expected Pace Percentage
• Remaining

You should now have this:

Now let’s stack the series and display the value labels:

Now we’re getting there!

So now let"s change the series color so that we have a smoother transition between our current and our pace:

Almost there!

Now we want to hide the value labels for the Expected Pace Percentage and Remaining. Looker doesn’t have that feature, so we’ll bypass it by…

…changing the font colors with the corresponding colors of the series!
And while we’re at it, let’s increase the font size!

Well there we ar…

Wait… I can see that the value label for our “Expected Pace Percentage” is overlapping and we can see it… Hmm being the analytics professional that we are, we can’t allow that.

Again, Looker does not offer the feature to hide labels so let’s use the good old value format trick!

In the Value Format input that string:

#.00%[>0.10];#%[<0.10];[<0.06]

This will hide value below 6% and display values between 6% and 10% without decimals.

Now final touch!

Let’s change the series labels to match our original screenshot back at the beginning:

There you go!!

I Hope that’ll help someone!