Course: Creating an interactive sales dashboard in Tableau
In this Tableau Academy course you take up the role of a sales operations analyst who has been tasked with creating an interactive global sales dashboard for the retail chain they work for.
For information on how Tableau Academy works please refer to our Welcome To Tableau Academy Guide.
The Project
This section sets out the information about the project you’ll be undertaking in this lesson.
Your Role:
You’re working as a Sales Operations Analyst for a global retail chain that sells a range of office equipment. Part of your role is to produce reports and dashboards for the global sales leadership team to help them manage the business and identify reasons why sales are growing or falling in particular areas or categories.
The Brief:
The sales leadership team currently receive a variety of sales reports from different regions in different formats, they’ve asked for a global report to be created which allows them to see and compare sales across regions and products for the entire company.
Required Data:
The global Business Intelligence team have set up a daily export of the combined sales data from all regions in .csv file for you. This contains line by line sales items and is the trusted and signed off source of sales data for the entire company.
The file is refreshed each day at 2.30am GMT with the previous days sales data added, the file location and name is always the same. All currencies have been converted to GBP and all date formats to DD-MM-YYYY as the globally agreed formats within your company.
Project Requirements:
The following requirements have been agreed and must be met:
1. A single page report containing a global view of total sales, profit and orders (sales volumes) per week for the past 12 months.
2. Ability to view each of the above by individual Geographies (Country), and Products (Sub Category).
3. Ability to see the breakdown of sales, profit and orders each week by Geography (Country), and Product (Sub Category).
Course Files:
You can download the files required for this project from here: Tableau Academy Course Files
In particular you will need to download and save the data file here: Global Sales Data.csv
Solution
The rest of this course now walks you through building out an interactive dashboard in Tableau Desktop which meets the requirements above, once you’ve completed the course you’ll have built a fully interactive sales dashboard and been introduce to many of the key functions and capabilities of Tableau.
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.
Step 1: Connect to the data and create the main global sales chart
1.1 - Our first task is to connect to the provided data source in Tableau, make sure you’ve downloaded a copy of the global sales data.csv from above to your PC and that you have a copy of Tableau Desktop or Public installed.
If you’re new to Tableau the first thing you’ll need to understand is how to get data into Tableau. If you’re coming from mainly using spreadsheets or similar software it’s worth knowing that Tableau connects to data stored in other files, databases or systems rather than providing a way for you to input data as you may be used to.
The great news is that Tableau makes it really easy to connect to almost any data source, you generally just need to point Tableau at the data you want and it does most of the work for you.
In this course we’re going to connect to a file that contains the data we want, specifically the ‘csv’ file you should have already saved from the course files folder.
Open Tableau Desktop or Public.
On the blue Connect menu on the left select ‘Text file’ in the ‘To a File’ section then navigate to the location you saved the file above to, select the file and click Open.
The data from the file will now be brought into the preview screen which you should see open up automatically. This page previews your data and allows you to make initial changes if you want to.
We don’t need to do anything here and we’ll cover this screen in later courses so we can just click ‘Sheet 1’ at the bottom of the screen (it should be Orange and beneath the ‘Go To Worksheet’ pop up) which will take us to the main Tableau interface with our data loaded ready to work with.
Video 1.1: Connect to data (click full screen to enlarge):
1.2 - This has now opened up a new Sheet in Tableau for us to start creating our charts. To start with we’re going to add a filter to just show the last 12 months data as per the project requirements.
The data file that we’ve loaded contains a lot of data but we’re only interested in the data for 2020 for this project. If we wanted we could remove the data we don’t want from the data file, or create a copy with just the data we want however Tableau can take care of this for us using data filters which we’ll take a look at now.
On the screen that’s appeared you should be able to see your data pane at the far left of the screen, this is a list of data fields that exist within your data and we’ll see how to use these shortly. Next to those you should see a number of ‘cards’, namely Pages, Filters and Marks, we’re going to focus on the Filters one for this part of the lesson.
As you might have expected the Filters card allows you to filter your data, Tableau provides many options for filtering your data but we’ll just use a simple date filter to introduce you to this.
If we want to add a filter for a particular field in our data we simply drag it from the data pane and drop it onto the Filters card, a menu then pops up allowing us to set what we want to filter within that field as well as some additional options.
In this project we want to filter on our ‘Order Date’ field and only include data with an order date that is in 2020, let’s have a look at how to do this:
Click the Order Date field in the Data Pane on the left and drag it over to the Filters shelf.
Select Years and press Next >.
Tableau automatically displays a list of the years within the data, we only want 2020 so tick the check box next to 2020 only and then click OK.
Note: The data in this example project is static so never changes, in the real life project you’d want to only show data from the last 12 months, this would then be a dynamic filter which updates automatically without you having to change the filter every time the data changes but we’ll cover that in a later project when we look at more advanced functions.
You should now see that a blue pill has been added to the Filters card, other than that it won’t look like anything has changed.
To see what has happened double click on the Order Date field in the data pane (note: in the data pane, not the filters card).
A table should appear showing the years in the data, with the filter applied it should only show 2020. Click the blue pill in the Filters card to bring up the menu and then click Remove (a quicker way is to just drag the pill off the filter card to remove it as seen in the video below).
You should now see that 2019 also appears in the table on the main panel so you can see that your data had indeed been filtered.
To get your filter back follow the steps again, or click the undo button (or press ctrl + z) three times to get back to where you were.
Note: A really handy feature of Tableau is the unlimited undo option, this essentially allows you to keep pressing undo until you get back to where your file was when you opened it. This is really useful as you’re not limited to a certain amount of undo steps and it’s saves a lot of time and hassle if you get into a muddle and need to go back!
Video 1.2: Add date filter (click full screen to enlarge):
1.3 - Now we’re going to create our main chart to show the total sales per week for the last 12 months.
We’ve got our data and filtered it so we can now go ahead and start building some charts which is exactly what Tableau is for!
In this part of the project we show you how easy it is to explore and visualise your data using drag and drop functionality.
In Tableau we use Sheets to create individual charts, this can be confusing if you’re new to Tableau but once you know that each Sheet contains a single chart it makes it much easier (it would have been simpler if Tableau called their tabs Charts rather than Sheets though!). Later in the project we’ll show you how you combine multiple sheets and create multiple charts that can interact with each other.
To create a chart we simply drag the relevant fields we want onto the relevant part of the main pane in Tableau. For our first chart we want to show sales for each week so we’ll want the Order Date field on our X axis (which Tableau calls Columns) and our Sales field on our Y axis (which Tableau calls Rows), you’ll also see that Tableau automatically picks the best chart type based on the data we add.
Right click on Order Date in the Data pane (this will allow you to select the aggregation you want, you can just left drag it and then edit the options but right click dragging is much easier, especially when working with dates), hold and drag it onto the Columns shelf.
Select WEEK(Order Date) from the green options in the bottom half of the table and press OK. As Tableau has recognised your Order Date is a dates field it offers various options for how you want to display and aggregate the dates on your chart, we’ve picked the option above as it will aggregate our dates to weekly data on the chart we’re creating which is what we want for this chart.
Next, drag Sales from the Data pane to the Rows shelf and it will default to creating a Sum of the sales for each week.
You should see that Tableau has automatically added up the sales for each week in our data and plotted this as a line chart for us, this is how easy it is to create charts to analyse and visualise your data in Tableau!
Tableau makes a pretty good job of determining what to show based on the type of data we select, however you can also change this if you wish. We’re happy with what Tableau has selected but we could change the cart to a bar chart if we preferred and could also change it to show the average sales each week rather than the total but we’ll explore those in later projects.
Video 1.3: Create weekly sale chart (click full screen to enlarge):
1.4 - Next we’re going to add profit onto the colour dimension of the chart, this will allow users to see how both sales and profit have been performing at the same time.
Something else Tableau does really well is enable us to create charts that show additional information and context without making our charts too busy and complicated.
Take a look at the chart we just created, we can see when sales have increased and decreased pretty easily but what if we want to know whether our profitability has also gone up or down at the same time.
We’d normally expect sales and profit to move in the same direction and have similar trends but what if our sales have increased as we’re offering huge discounts so selling a lot but not making as much profit on those sales?
We could just add profit to the chart as another line which would work ok but a much more effective method would be to colour code the line in our chart to show when profit was increasing or decreasing and by how much which would then give us much more information.
With Tableau we can do this in just a few seconds and with just a few clicks! This is how:
Click Profit in the Data pane and drop it in onto the Colour card.
We’d like to change the colour from the default choice so click the Colour card and then the Edit Colours… button.
From the Palette drop down menu select the Red-Black Diverging option.
Tick the option to Use Full Colour Range and press OK.
Video 1.4: Add profit to the chart (click full screen to enlarge):
1.5 - We’ve got what we need for our first chart, we’ll now clean up the formatting by adding labels and removing any clutter that we don’t need.
Tableau does a pretty good job of creating clean and uncluttered charts by default. However, we’re big fans of only keeping things on our charts that add value. For example, rather than having our axis listing values on the left and taking up valuable screen space we prefer to remove these and add labels to the biggest and smallest values on the chart.
This still allows users to see the range of values on the chart without taking up much space, if they need to see the specific numbers for a data point they can hover over or click on it to get these:
Click the labels button at the top of the screen to turn labels on (it’s the T in a dashed box on the menu bar).
This has put all the labels on which looks messy so click the Label card and choose the Min/Max option. We know our users prefer to see the biggest and smallest value so we chose that option, you may want to select different option though e.g. just showing the first and last labels.
Rather than deciding on formatting for each individual chart, report or dashboard we’ve developed an internal style guide that we work to in order to keep our reports consistent.
This style guide lays out the formatting we should apply to each element of our charts, below we go through making each of the changes our style guide depicts and in a later course we’ll show you how to create your own style guide and apply these by default to your Tableau workbooks.
First up our style guide tells us what fonts, font sizes and weights to use for each area of our chart. To format the fonts used in your workbook:
Click Format from the top menus and select Workbook….
The Format Workbook menu open where the Data pane was on the left, this allows us to set formatting across our workbook.
Set options as shown in the screenshot below for each.
Click the x at the top to close the menu (you can watch the video below to see this in action):
For some areas we can’t set the formatting via the format workbook menu so have to set it manually, for example we want to format the font used on our axis and remove the axis labels which we can do as follows:
Right click the Y axis (the one with Sales on it).
Select Format…
Set the Default Font to Segoe UI Light, Size 8 and the middle grey from the first column of colours as below.
Repeat for the X axis too.
Right click the X axis again.
click Edit Axis…
Delete the text in the Title field to remove it.
Close the Edit Axis window.
Close the Format menu on the left.
We also want to change the title at the top of the chart, this defaults to the sheet title but we want something that looks better and is more informative, edit the title as follows:
Double click the worksheet title at the top of the chart (it’ll say Sheet 1 at the moment) to start editing it.
Replace the text with the following (you can copy and paste from here):
GLOBAL SALES
Total global sales over last 12 months
Using the text formatting options at the top of the open dialogue box set the following:
Highlight the first line and set the font as Segoe UI Semibold, Size 14, and colour Black
Highlight the second line and set the font as Segoe UI Light, Size 10, colour Black
Highlight all text and select centre alignment.
Press OK to close the sheet title dialogue box.
The sheet title should now have updated to what you’ve just set.
Video 1.5: Formatting and cleaning up (click full screen to enlarge):
1.6 - Finally, we want to add a weekly average line so users can easily see if the sales each week is above or below what we might expect.
Click the Analytics tab on the left of the screen next to Data.
Click and drag Average Line onto the chart, as you do an Add a Reference Line box appears drop the Average Line onto the Table option, this will give us an average line across our entire chart.
Right click the Average line that has appeared and click Format…
Change the line to the thinnest dashed line and then change the font to size 8 and Italics and close the Format Reference Line menu.
Notice how this lets us see that the majority of our sales are in the 2nd half of the year, up until about July sales are below the 12 month average and after they are generally above it.
Video 1.6: Add average line (click full screen to enlarge):
That’s the first step complete! You should now have a chart showing the global sales and profit each week which looks like this:
Step 2: Enable users to see sales volumes as well as sales values
One of our requirements is to be able to see the sales volume as well as the sales value, we could add this as a second line on the chart but I find it tends to be misleading and confusing, instead we’re going to let users select which they want to see using a parameter and a calculated field.