Creating KPIs within a Single Sheet in Tableau
Exploring both table calculations and LODs in a single sheet.
Everybody loves a high-level KPI (key performance indicator) at the top of their dashboard. Some dashboards can consist entirely of KPIs too. Creating KPIs in a single sheet can be useful when you are creating a KPI dashboard to save you from having to create several sheets. They can be beneficial for many reasons including:
- The ability to do a quick comparison month over month etc.
- They provide quick answers to aid decision-making and discussions.
- They are straightforward to read.
- They allow you to question why.
- You can organise them to highlight critical business metrics.
- You can enhance them with visuals, shapes and colour to show trends over time but also highlight the ones that are performing beneath a threshold.
They should not be taken as the single source of truth of course. Typically, an underlying story is related to that metric and worth exploring for further context. For example, your prior month's sales could have dipped which of course, does not look good to the business. This could prompt us to ask why? Maybe, a new competitor launched on the market and this can be noticed in the dip? You may question what products had decreased in sales to see if they matched what my competitor is selling. Learning this may lead you to certain questions and action items.
A KPI dashboard might be the starting point for further exploration and analysis.
KPI with Table Calculations in a Single Sheet
When using table calculations, the key is to ensure that bring the fields into the view and set them to calculate correctly.
This example uses superstore data within Tableau and is based on sales.
- Create a current month sales calculation
IF LAST()=0 THEN SUM([Sales]) END
2. Create a prior month sales calculation
Hack: Duplicate the last as a template, rename and add 1 for prior month
IF LAST() =1 THEN SUM([Sales]) END
3. Create a % difference sales calculation
(WINDOW_SUM([Current Month Sales]-WINDOW_SUM([Previous Month Sales]))/
WINDOW_SUM([Previous Month Sales]))
Ensure that you change the number format of this. You can do so by right-clicking the field in the data pane > default properties > number format.
Select percentage and configure the number of decimal places that suit you.
4. In this example, we will build a KPI with sales across the months. Drag sales to the rows shelf, and continuous months to the columns.
Hack: Right-click and drag order date and you can then select continuous months.
5. Drag all three of these fields to your details shelf.
This allows them to be referenced correctly, especially as we are creating this in a single sheet.
6. Edit the table calculation for each calculation in your marks card
Click on the drop-down which appears when you hover by the triangle > select edit table calculation > ensure that each is set to specific dimensions
7. Now we want to get the text in the header. Edit the title and select the fields that you want to show above the line chart. In this example, I will use this format:
SALES
<AGG(Current Month Sales)>
<AGG(% Difference Sales)> vs PM <AGG(Previous Month Sales)>
You can select the fields by hitting insert. Ensure that you give your KPI a title.
As a result, your KPI title will look like this:
Overall, you will have a KPI in a single sheet as such:
8. Optional — Do some cleaning to remove grid lines
Right-click on the canvas > format > go to sheet grid lines and turn them on and then off to remove them.
Do the same for axis rulers.
9. Optional — Do some cleaning to remove the headers
Right-click on each axis header and uncheck ‘Show Header’.
There you go! You have a KPI in a single sheet, with table calculations that should look similar to this:
You can go the extra mile and edit this some more by adding shapes, icons, and different colours to enhance your KPI.
KPI with LODs
Arguably, KPIs can be a bit more robust however, you do have to use filters in context which can cause problems if you need some to be in context and not others.
This example uses superstore data within Tableau and is based on sales.
- Create a current month sales calculation
SUM({ FIXED : SUM(IF DATETRUNC(‘month’,[Order Date])= {MAX(DATETRUNC(‘month’,[Order Date]))}
THEN [Sales]
END
)})
2. Create a prior month sales calculation
Hack: Duplicate the last as a template, rename and add 1 for prior month
SUM({FIXED: SUM(IF DATEDIFF(‘month’,DATETRUNC(‘month’,[Order Date]),{MAX(DATETRUNC(‘month’,[Order Date]))})=1
THEN [Sales]
END )})
3. Create a % difference sales calculation
([Current Month Sales — LOD]-[Previous Month Sales — LOD])
/
[Previous Month Sales — LOD]
Ensure that you change the number format of this to a percentage as above in the table calculation example.
4. In this example, we will build a KPI with sales across the months. Drag sales to the rows shelf, and continuous months to the columns.
Hack: Right-click and drag order date and you can then select continuous months.
5. Drag all three of these fields to your details shelf.
This allows them to be referenced correctly, especially as we are creating this in a single sheet.
6. Now we want to get the text in the header. Edit the title and select the fields that you want to show above the line chart. In this example, I will use this format:
SALES
<AGG(Current Month Sales — LOD)>
<AGG(% Difference Sales — LOD)> vs PM <AGG(Previous Month Sales — LOD)>
You can select the fields by hitting insert. Ensure that you give your KPI a title.
As a result, your KPI title will look like this:
Overall, you will have a KPI in a single sheet as such:
7. Optional — Do some cleaning to remove grid lines
Right-click on the canvas > format > go to sheet grid lines and turn them on and then off to remove them.
Do the same for axis rulers.
8. Optional — Do some cleaning to remove the headers
Right-click on each axis header and uncheck ‘Show Header’.
There you go! You have a KPI in a single sheet, with LODs that should look similar to this:
Troubleshooting Tips
One of the best ways to ensure that your KPI is calculating and working as it should is to build a grid. You can do this before, during or after building your KPI but generally, it is the first thing that you should do when you notice that something is wrong.
You want to drag in the order date set to continuous months. You then want to turn this discrete to be able to drop fields into the table as such in the LOD example:
Notice how the same view is different in the table calculation version:
The view differences are specific to the way in which both calculations are built.
You can decide on which approach you like best but hopefully, this is a good starting point for creating a KPI dashboard.
