Ads Top

Creating NPS Gauges in Tableau

This weekend, Rajeev Pandey, a former Tableau Featured Author and regular in the Tableau community, posted a question on the Tableau Community forum asking if anyone had ever created a gauge to show Net Promoter Score. Here’s the image he shared.


He also reached out to me directly to see if I could help, so I figured why not give it a go.  

What is NPS?
Before jumping into how to create this gauge in Tableau, let’s start with an explanation of NPS. According to Medallia.com, “The Net Promoter Score is an index ranging from -100 to 100 that measures the willingness of customers to recommend a company’s products or services to others. It is used as a proxy for gauging the customer’s overall satisfaction with a company’s product or service and the customer’s loyalty to the brand.” I won’t go into detail about how NPS is calculated, but if you’d like to learn more, you can check out the Medallia’s full explanation.

While there are a number of different ways to visualize this metric, one of the most common seems to be a sort of gauge as seen in the image above.

The Chart
Let’s start with some observations about this chart.
  • First of all, it looks basically like a half of a donut chart (and donut charts in Tableau are essentially pie charts with a hole in the middle).
  • The first slice of a pie chart in Tableau always starts at 0°, but this chart would need to start at 270°.
  • The values of the chart will need to go from -100, starting at 270°, to 100, ending at 90°.

So, how to create it? The biggest challenge of this chart is the fact that it needs to start at 270°. I decided that my approach would be to create a donut chart with 5 pre-defined slices, as shown below.

  • Slice 1 – Variable sized slice starting at 0° and ending between 0° and 90°. This slice will be used to show the second part of the NPS (if the value is greater than 0). Otherwise, this slice will have 0 size, making it invisible.
  • Slice 2 – Variable sized slice starting and ending somewhere between 0° and 90°, after slice 1. If NPS is less than 100, then this slice will be visible and will appear in a light grey color.
  • Slice 3 – Hidden slice starting at 90° and ending at 270°. This slice will always be 180° and will always be the same color as the background, rendering it invisible, essentially making it a half-donut.
  • Slice 4 – Variable sized slice starting at 270° and ending between 270° and 360°. This slice will be used to show the first part of the NPS (values between -100 and 0).
  • Slice 5 – Variable sized slice starting and ending somewhere between 270° and 360°, after slice 4. If NPS is less than 0, then this slice will be visible and will appear in a light grey color, like Slice 2.

Using this relatively simple idea, I created a very small data set containing five records, one for each slice, with the following information about each:
  • Slice # – Numeric identifier of the slice (1, 2, etc.)
  • Slice – Name of the slice (Slice 1, Slice 2, etc.)
  • Details – Description of the slice and how it’s used.

Strictly speaking, we could get by with just Slice # in the data set, but I decided to add a bit more information to make it less confusing to someone using the template.

Notice that there are no measures in the data set. We’ll create the measure within Tableau, based on the NPS score. Note: I’m assuming that the user will have an NPS score which can be fed to the visualization. For demonstration purposes, I’ll just create a parameter allowing the user to select an NPS.

The Calculations
Calculating the pie chart size measure is a matter of some relatively simple math. To simplify my formulas, I created five calculated measures, one for each slice. Here are the formulas for each.

Value_Slice_1:  IF [NPS]>0 THEN [NPS] ELSE 0 END
Value_Slice_2:  IF [NPS]>0 THEN 100-[NPS] ELSE 100 END
Value_Slice_3:  200
Value_Slice_4:  IF [NPS]<0 THEN 100+[NPS] ELSE 100 END
Value_Slice_5:  IF [NPS]>0 THEN 0 ELSE 100-[Value_Slice_4] END

Note: “NPS” is the name of the parameter noted earlier.

Then, I combined these values into a single measure, which I called Chart Value:

CASE [Slice #]
WHEN 1 THEN [Value_Slice_1]
WHEN 2 THEN [Value_Slice_2]
WHEN 3 THEN [Value_Slice_3]
WHEN 4 THEN [Value_Slice_4]
WHEN 5 THEN [Value_Slice_5]
END

Finally, I created a calculated field called Color:

IF [Slice #]=3 then "None"
ELSEIF [Slice #]=5 OR [Slice #]=2 THEN "Grey"
ELSEIF [NPS]>[Highest Normal Value] THEN "Green"
ELSEIF [NPS]>[Highest Bad Value] THEN "Yellow"
ELSE "Red"
END

Note: This formula references two other calculated fields, Highest Normal Value and Highest Bad Value, which refer to the maximum “Yellow” NPS score and the maximum  “Red” NPS score respectively. In this case, I’ve used values 60 and 20 per Rajeev’s requirement.

Next, based on these calculations, I created a very simple pie chart, as shown below:


I then changed to a dual-axis chart, with the second axis showing a manually sized white circle.


Note: I’ve colored the NPS score the same color as the gauge. Doing this required that I create three separate calculated fields as follows:

Value_Bad:  IF [NPS]<=[Highest Bad Value] then [NPS] END
Value_Normal:  IF [NPS]>[Highest Bad Value] and [NPS]<=[Highest Normal Value] then [NPS] END
Value_Good:  IF [NPS]>[Highest Normal Value] then [NPS] END

I then combined those into the Label as follows: <Value_Bad><Value_Good><Value_Normal>. As only one of these calculated fields can have a value, you’ll only ever see one color.

With that complete, I had the basic NPS chart in place.



But, I was missing one key component. The original chart had a small key right below the gauge showing where the values would change from red to yellow to green.
So, how do we create this? I experimented with a few different options, but all seemed to require use of a dual-axis and, since I was using the dual axis for the hole in my donut, I could not find a way to make it work. In the end, I decided that the best approach would be a custom image with a transparent background, which would float over the pie on a dashboard. While I’d definitely prefer not to having to piece together floating elements to make the chart work, I just couldn’t find any other way (If anyone reading this has another idea, I’d love to hear it.)

Since I would be floating an image on top of the chart, I decided to just use the image to double as my donut hole. The image I ended up creating (using mostly PowerPoint and a little Paint.NET) looks like this (the checkered background indicates transparency).



With this element acting as the donut hole, I removed my dual axis and the center circle, taking it back to the original pie chart. 

From here, it was just a matter of adding the pie to a dashboard and floating the image over top of it. Finally, I added one more sheet to display the actual NPS value, which was then floated over top of the image. 



Here’s the final result:




Required Files
If you have a need to create a gauge like this, then feel free to download my examples from Tableau Public. You can also find the Excel template here and the image here. If your customer requires different ranges for bad, normal, and good, then that will require some slight changes to the image. In addition, if you need to change colors (yes, I know that traffic light colors aren’t necessarily the best option), then you can fairly easily recolor the image.

Ken Flerlage, July 25, 2017.


9 comments:

  1. Ken,
    Being novice in Tableau, I wasn't able to create the pie chart exactly as displayed. Mine looked like a full pie chart as opposed to the 'half moon' you had created. Am I missing something simple?
    Ed

    ReplyDelete
    Replies
    1. Mine's a full pie chart as well. I've just colored the bottom half (slice 3) white so that it blends into the background, making it invisible.

      Delete
  2. Great tutorial for building an NPS guage. I'm currently doing something a little different (converting the scale of -100 to 0 to 100) for any kind of value or metric to display (counts of something, percent progress, etc.), but my greatest question has to do with "how did you remove the wedge highlighting when the mouse hovers over the gauge?" I cannot figure this out for the life of me. I am currently using a professional license for v10.1.3. Thank you for any assistance you may offer!

    ReplyDelete
    Replies
    1. Simple trick--just float a Blank over top of it. It will prevent users from interacting with the pie.

      Delete
  3. This is great Ken. I'm not a fan of NPS, nor Gauges...but still find this to be incredible work and I'm certain this will be useful for many.

    ReplyDelete
  4. Ken, this is awesome! I tried to replicate and the pie kept looking like it was on it's side and then I realized that my dimensions/measures on the marks were not in the same order as what you had (I had them ordered Color (on color mark), Avg(Chart Value) (on angle), Slice (on detail)) so when I re-ordered them to look like your workbook (Slice (on detail), Color (on color), Avg(Chart Value) on angle) the chart flipped back to what it should look like. Can you explain why the order of the marks has this impact?

    ReplyDelete
    Replies
    1. Glad you found it valuable. The order of the pills on the marks card defines the precedence in the sorting hierarchy. So, by moving Slice to the top, it took precedence on the sorting.

      This blog post explains a little further: https://public.tableau.com/en-us/s/blog/2016/03/order-things-how-control-layout-marks

      Delete

Powered by Blogger.