How to Develop a KPI Dashboard in Tableau

Note: This post is fifth part of Building KPIs dashboard Series.

• I. What are KPIs

• II. KPI Comparison

• III. Drilldown into KPI

• IV. Wireframing A Dashboard

• V. Build KPI Dashboard

• VI. Best practices for KPI analysis

Today's #MakeoverMonday was awesome. We were supposed to improve upon a KPI scorecard for Washinton DC Metro service. Here is what I made:

I got a few positive nods from the community and as the encouragement, I would like to share with you how I made the scorecard above.

The 'Makeover' Part

The most important thing about a MakeoverModay is the 'Makeover'. Because we are always improving upon a previously created visualisation, we are always under pressure to come up with a better one the what was earlier created. The original one published on DC Metro's website was a good one actually, except it violated some of fundamentals of data visualisation and had some vibility problem:

• Radial bar charts are problematic with absolute numbers

• Dashboard at the glance isn't showing trends

I encourage you to skim through definitions of KPIs used in the dashboard in page 1 of this document before you read further because unless you understand what you are creating, it isn't much useful to understand how you are creating.

Data Preperation

Done? Great. Let's get back to dashboard design now. The very first problem happened when I downloaded the data and had a look at it. The data was in a wide format. 9 KPIs to be tracked and and each KPI has target and actual so data ended up with 9 X 2=18 columns. Tableau hates wide format data (or at least I do). So we will have to transpose data to use in the Tableau. Fortunately, when connected to Excel, Tableau let's you transform and pivot the data from wide format to tall format, so that's what I did

Now the layout of the data is goal, month, value so each goal is divided into target and actual which are further divided into time series. Since column names have become dimension after pivoting the data, we will have to identify which rows belong to target and which belong to actual. A little text cleaning is also required. Write these three calculations to accomplish goals:

Also, we are going to need a month column to create time-series analysis. We have Year and Month columns in the data. Let's examine them and see if they can be of any help

Year: CY 2014, CY 2015, CY 2016

And each year has data broken by months and also a subtotal for YTD

Month: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, YTD

Which looks okay to me, we will need to write another DATEPARSE calculation to use these text fields to create a date filed named Months:

Data Visualization

Voila! The fun begins now. Let's start with goals to be accomplished with the visualization

• To glance performance of each of 9 KPIs against the target

• To understand if performance is improving over the period of time

• To the deviation from the target and the trend of deviation

Looks pretty OK for a KPI Dashboard. Let's get started with creating a blueprint, though some of you might not be used to the concept of creating a blueprint before you design, I always find it a pretty good idea to create a design-skeleton before we actually get into work

For every KPI, there is a block, and within every block, there are four charts stacked vertically

• Chart 1 - Text Chart: latest month target vs actual

• Chart 2 - Bullet Chart: color coded target vs actual for latest month

• Chart 3 - Bar Chart: macro changes in metric performance over years. YTD values only

• Chart 4 - Sparkline: month of month target vs actual with colour code (how to create sparkline)

And finally put all pieces together and bind them into a pixel perfect single dashboard. This is it.

Current Month Text Charts

Currently target and actual are seperated by a domension Value Type. Calculate these two measures for target and actual separately

And also a set to be used in filter for latest month

This is the configuration for the text chart on the top

Current Month Bullet Chats

This is plainly a bar chart with a reference line. Length of bar chart is equal to actual and reference line is placed at target position. Further actual bar is colour coded with target achievement. If achievement is less than 100% then make the bar red, else green. Here is the configuration:

While colour coding you have to keep two things in mind. There are targets which you always want to exceed like On Time Performance. So if the actual value is greater than target then the colour code is green else red. However, there are safety metrics like crimes which are green if they are below the target and red of above the target. So for these inverse metrics use reverse option in on Color Marks. These are the inverse metrics for your reference:

• Crimes per 1 Million Passengers

• Customer Injury Rate per 1 Million Passengers

• Employee Injury Rate per 200K Hours

Ghost Bar Charts

Yeah, Ghost Bars, (as in noun). I made those three greyed out bars keeping in mind that end user will look at those graphs only on demand. Also, data is not going to change very frequently in them. Only current year YTD chart will change every month and old years data will never change. So sort of, after some time, end user will remember the values on them already. I know I started preaching end user design which I should not, at least not in this post. BTW, Andy Cotgreave loved those ghost bars ;) 

Here is the layout for Ghost Bars. Only thing you need to remember about this chart is that filter out all other months except the 'YTD' entry because we already have YTD subtotal in the data

MoM Sparklines

This is a simple sparkline with dual axis. Doesn't need much explanation, so here is the configuration:

Put It Together

Alright! Congratulations. You have ingredient ready in the form of 36 charts (four for each goal). Now is the time to put them together. Refer to the blueprint once again.

• Create a dashboard sheet and float 9 vertical container on it in a 3X3 grid

• Set size of each container to 232 X 241 (w x h)

• Tile 4 charts for each goal in it's container

• Edit height of each chart as follows: Chart I 100, Chart II 25, Chart 3 50, Chart 4 50

A little bit of further formatting and pixel perfection and you're good.

I hope you enjoyed reading the first article in the series. Read part II of the series Comparing KPIs. Any questions? Hit below in comments. Cheers.

If you are a project manager and want to hire me to build a KPI Dashboard for your department, contact me to schedule a discussion.