Creating A KPI Dashboard

For information on how Tableau Academy works please refer to our Welcome To Tableau Academy Guide.

The Project

In this lesson you’ll be creating a KPI dashboard which enables your organisation to track four key metrics that have been selected and identify trends for further investigation, the KPI dashboard will be displayed around the organisation’s head quarters and be available to the entire organisation.

Your Role:

You’ve been seconded to the Corporate Strategy team within an organisation that sells products to businesses around the globe. The Corporate Strategy team is currently focussed on aligning the strategy and goals across the company and has been tasked with ensuring this is clearly defined and putting a programme in place to ensure each part of the organisation is working towards this.

Your role within the team is to streamline the number of reports that are produced and distributed throughout the organisation, a key part of this is also to reduce the number of Key Performance Indicators (KPIs) that are used and tracked across the organisation and to produce a dashboard that makes the selected KPIs visible to everyone within the organisation.

The Brief:

A recent analysis by the Corporate Strategy team identified that the number of reports and KPIs in use across the organisation is creating confusion and is resulting in different parts of the organisation focussing on different areas.

Four KPIs have been agreed as part of the wider project and you’ve been tasked with building a dashboard that shows theses KPIs along with the trends which will eventually form the basis of a streamlined reporting output for the entire organisation.

The dashboard will be used to monitor the KPIs and identify any trends for further investigation, it is anticipated that a later phase of the project will build out the ability to investigate the reason for any underlying trends and put in place actions to address any areas of concern.

Required Data:

The data you need for this project comes from a number of sources which have been combined together into a single view within your organisations reporting data warehouse.

This is automatically distributed to you each month in a .csv file called KPI_Data.csv and can be downloaded from the lesson files section below.

(Note: As it’s not possible to connect to a database in Tableau Public we use a csv file to make the lesson accessible to those without Tableau Desktop. In a later lesson we’ll look at how the data is taken from the source data and combined and cleaned into the required format using SQL or Tableau Prep Builder).

Additional Information:

The four KPIs that have been selected are as follows (more details are included in the relevant sections when you come to build these):

• Revenue: The total value of all sales over the last 12 months from the latest reporting date.

• Profit Margin: The ratio of profit over sales in the last 12 months from the latest reporting date.

• Customer Satisfaction: You have a supplier who is responsible for carrying out a set amount of customer satisfaction surveys each month. Each of these has a calculated Customer Satisfaction score out of 100, the KPI is the average score across each country in the month of the latest reporting date.

• Employee Satisfaction: Each employee in your organisation is asked to give an overall score of how satisfied they are with the organisation, their working environment and their role. This is a simple score of 0-10 and is used as a monthly pulse check. The KPI for this is the average score across each country in the month of the latest reporting date.

The reporting date is the last calendar day of each month, the latest reporting date is always the last calendar day of the last completed month.

This project also uses a number of icons for the dashboard these are available to download below, as well as a Tableau template file that you can use for this project.

Project Requirements:

The following requirements have been agreed and signed off by the project stakeholders and must be met:

1. All four KPIs must be displayed clearly on a single dashboard so anyone cans see the latest results at a glance.

2. Users should be able to see at a glance how each KPI has changed from the previous month.

3. An overall view of the KPI result for each of the 12 months preceding the latest reporting month should also be provided, for clarity this means a total of 13 months so users can see the trend over the past 12 months but also compare to the same month in the previous year.

4. Users must be able to filter down by the organisation hierarchy to see results by a single Business Unit or Country.

5. Users should be able to see for any given month how the underlying Business Unit or Countries contributed to the KPI trend, i.e. which ones improved or declined and by how much.

Lesson Files:

You can download the files required for this project from here: Tableau Academy Google Drive - Lesson 3

In particular you will need to download and save the csv data file here: KPI_Data.csv

Solution

The steps for the solution are shown below, a short video follows each section showing that part of the solution being done. Additional info is shown in highlighted paragraphs like this one.

The first time you come across a new requirement (e.g. to create a calculated field) detailed instructions are provided, in subsequent steps these don’t tend to be repeated unless required. We highly recommend watching the accompanying video with each section to see exactly how each step works and what you need to do.

Step 1: Download the template and connect to the data

Step 1.1 - For this project we’ve created a template that you can use to save you setting the formatting and layout of the dashboard yourself, in this first step we’ll download this and connect to the required data source.

Make sure you’ve downloaded and saved both the KPI_Data.csv file and KPI Dashboard_Template.twbx files from above.

Open the KPI Dashboard_Template.twbx file in either Tableau Desktop or Tableau Public.

Click Connect to Data in the Data pane (Tip: Use the Ctrl+d shortcut key to open this quickly), on the left hand side click Text File, then navigate to where you saved the KPI_Data.csv file and click Open.

The data should appear in the preview panel, there’s nothing you need to do with the data as it’s already been prepared so simply click the Template Sheet tab at the bottom of the screen to get started!

Once you start to create your charts in the next step you’ll notice our standard design has already been applied for you. If you click into the KPI Dashboard tab you should also see that we’ve created a template for the dashboard that contains the sections, text and icons that you’ll need to save you doing this manually.

The icons on this page are simply Image Objects that we’ve placed on the dashboard, if you would like to re-create them yourself each of the icons are contained in the Lessons Asset folder and you simply drop an Image object onto the dashboard, select the file and then set the positioning/layout the same as we have in the template.

This is the same with the text headings/sub headings you see. We simply added a Text Object for each and then entered and formatted the text as required.

Finally, the grey borders you see are just Blank Objects that we added. In previous lessons we used dashboard Containers to automatically place our content, in this lesson we show you how to do it manually for greater control over the positioning and layout of your dashboard.

Video 1.1:

Step 1.2 - The data we receive is split by countries, each of these countries is assigned to a Business Unit but this isn’t present in our data. In this step we’ll group our countries into their Business Units so that we can easily filter by them later.

Click the New Sheet button at the bottom of the screen.

Drag Country from the Data pane and drop it on the Rows shelf or area.

Open up the Country to Business Unit Mapping.xlsx file from the data folder in the Tableau Academy Google Drive - Lesson 3

In here you should see a list of Countries in the first column and the Business Unit in the second. For example you should see that both UK and Ireland belong to the UK&I business unit:

Back in Tableau click Ireland on the new sheet you created with the list of country names, the scroll down until you see UK. Hold the ctrl key and click UK so that both UK and Ireland are selected.

Right click either and select Group. They should both now be grouped together under Ireland & UK and notice that a new Country (group) field has appeared. This is preserving your original list of countries but creating a new field where they are grouped into their respective Business Units -

Right click the new Ireland & UK ‘country’ that’s appeared, click Edit Alias and change it to UK&I as per the file.

Do the same for the remaining countries so that they’re all grouped correctly and then rename Country (group) field as Business Unit.

Note: Once you’ve created at least one grouping you can right click the new Country (group) field in the Data pane and select Edit group. This will allow you to edit/remove countries from groups if you make a mistake but can also be used to group the countries if you prefer.

Simply highlight the countries you want to group in the list using the ctrl key to select multiple entries and then click the Group button. You can then rename the groups to reflect the naming in the mapping file.

Once done we want to combine the Country field with the new Business Unit field to create a hierarchy between the two. If you drag the Country field and drop it on top of the Business Unit field a Create Hierarchy box appears, change the Name to Organisational Hierarchy and press OK.

This will now appear in your Data pane with the Business Unit and Country below it, this has created a link between those two fields as a hierarchy so when you add them to a view you can drill up/down between them.

Video 1.2:

Step 2: Create the Revenue KPI and trend charts

Step 2.1 - Create the revenue KPI, in this step we’ll create the calculations and charts needed for the revenue KPI as defined in the project information above.

If you’re not already on it click the Template Sheet tab at the bottom of the screen and rename it as Revenue KPI.

We first need to figure out the latest reporting date, this is simply the latest date in our data and we can use a Fixed Level of Detail calculation to find this. Click Analysis from the top menu and Create Calculated Field (Tip: Use Alt, then a, then c to access this quickly).

Name the calculation Latest Date and add the following calculation:

{FIXED : MAX([Date])}

A Fixed Level of Detail calculation allows us to tell Tableau to set a calculation at a set level regardless of what we have in the view, without this Tableau will use the fields we add to our view to determine the results of the calculation.

Usually you would specify a field from your data to fix the calculation at, however by not specifying a field we’re telling Tableau to get the value for the entire data set, in this case it gets the maximum (or latest) date that’s in the dataset and we’ll use that to calculate the previous 12 months revenue.

We also need to know whether each row in our data was within the last 12 months or not to know whether to include it. To do this we’ll use a calculation that works out the difference, in months, between the latest date above and the Date for each row in our data.

Create a new calculated field and name it Months Before Latest and add the following:

DATEDIFF('month',[Date],[Latest Date])

We can now use this to calculate the total revenue across the previous 12 months.

Create a new calculated field and name it Revenue KPI_Current Month (we’re going to prefix all of our KPI calculation in the same way so it’s easier to keep track of them) and add the following calculation:

IF [Months Before Latest] <= 11
  THEN [Revenue]
END

The DATEDIFF calculation is getting the difference, in months, between the Latest Date and Date for every row in our data. If the difference is less than or equal to 11 (as our current month would be 0) then it’s getting the revenue for that month.

We also want to see what the Revenue KPI was in the previous month, we can use the same method as above to do this.

Right click the Revenue KPI_Current Month field in the Data pane and click Duplicate. Right click the duplicated field that’s appeared and click Edit.

Change the name of the calculated field to Revenue KPI_Previous Month and add the following calculation:

IF [Months Before Latest] <= 12
   AND [Months Before Latest] >= 1
  THEN [Revenue]
END

This is doing the same as our first calculation except it’s increasing the number of months it’s looking back to 13 (a difference of 12 months from our latest month) and then excluding the current month so that we’re still looking at a 12 month period (as the difference for that would be 0, so >= 1 makes sure we don’t include the latest month).

Drag both of the Revenue KPI fields and drop them onto the Text card (note: Hold the ctrl key to select multiple fields at once and then you can drag and drop them at the same time).

Click the Text card and then the ‘…’ next to the text to edit it.

Format the <SUM(Revenue KPI_Current Month)> line as Font: Segoe UI Semibold, Size: 18, Colour: Black.

Add a blank line below it then add the text “(from “ in front of the <SUM Revenue KPI_Previous Month)> and a “)” after it, then format the line as Italics and change the colour to #666666 (this is the 2nd last grey in the first column).

Centre align the entire text and then click OK.

We now just need to set the default number format for each of the new fields. Right click each of the Revenue KPI fields in turn then go to Default Properties > Default Number Format. Select Currency (Custom), set the Decimal places to 0 and under Suffix add an M to show that it’s £ Million that’s being shown...