How to Create a KPI Card (with Two Tricks for Showing % Change)
I find myself creating "KPI Cards" (like the one above) very often. In this blog post, I'm going to walk through creating one. If you've ever created one, you know there are some tricks to show the current year's value with the percent change...I'll show you two different ways of doing this.
If you want to follow along, please download my KPI Card from Tableau Public (or you can simply start with some Superstore data).
Let's take a closer look at the end-result of what we are going to build:
In this example, we are simply visualizing year over year. You can visualize any time frame or even allow users to toggle between time frames, but to keep things simple, we will just look at rolling year over year.
In the line chart, we've shown all 12 months and we've plotted sales as a black line for the most recent year (2024) and a gray line for the prior year (2023). In the information above the line chart, we've shown the total sales for the most recent year (2024) in a large black font (to match the black line), total sales for the prior year in gray (to match the gray line), and the percent change from the prior year to the most recent year. So this KPI card shows that in 2024, we had $746k in sales, which was an increase 21% increase from the prior year's value of $614k.
The first step I take in building one of these KPI cards is to create a calculation to show the different periods. Again, you could allow people to choose different time frames using a parameter and variations of the following calculation, but we will keep it simply and just look at the most recent year and the prior year. This is what the calculation looks like (note that all my calculations are numbered in sequential order of how I will create and utilize them):
1 Period
// Calculate previous year and current year IF DATEDIFF('month', DATETRUNC('month', [Order Date]), { FIXED : MAX([Order Date])}) <= 11 THEN 'CURRENT'
ELSEIF DATEDIFF('month', DATETRUNC('month', [Order Date]), { FIXED : MAX([Order Date])}) >= 12
AND DATEDIFF('month', DATETRUNC('month', [Order Date]), { FIXED : MAX([Order Date])}) <= 23 THEN 'PREVIOUS'
ELSE 'NA'
END
This calculation could be written in a number of ways, but it essentially puts the past 12 months into a bucket of "Current" and the previous 12 into a bucket of "Previous". Note that in many cases, you may decide to not include the current/partial month. In that case, you will need to update this calculation that ensures datediffs of 0 are not included and that the current period includes datediffs of 1 - 12 (not 0 - 11). And Previous would need to be adjusted accordingly. (With Superstore data, it simply runs until Dec 31, 2024). I'll also note that if you wanted to do the most recent 12 months versus the prior 12 months, the line chart will have to be created in a slightly different way than I created it. For this, I didn't want to focus on the line chart as much as I wanted to focus on the technique.
Next, I want to calculate the previous year's value and the percent difference year over year.
2 Sales - Previous Value
// Previous value LOOKUP(ZN(SUM([Sales])), -1)
This calculation just returns the previous row's value within a partition...within our view. If we showed 2023 and 2024, then on the 2024 row, it would show the 2023 value. If we showed it by months, then on the December row, it would show the November row's value.
Then we can use these to calculate the percent difference.
3 Sales - % Difference // % difference in sales (ZN(SUM([Sales])) - [2 Sales - Previous Value]) / ABS([2 Sales - Previous Value])
So this compares the SUM(Sales) for the row in question to the previous row's value. So for 2024, it would calculate the percent difference from the prior year, 2023.
We will create two more calculations. Ultimately, we want to display the percent difference by placing that calculation on the Text Card along with the raw sales value and the previous value. We will want to show the sales value in black and the previous sales value in gray to match the line chart. For the percent difference, it would be best if we could color-code it as red to show sales going down or blue for sales going up. (Color is the most important preattentive attribute that you can implement in data visualization...in my opinion). There is no way to automatically change the color of just this field and not impact the two sales values, so we will do a bit of a workaround, which I explain in this blog post.
Essentially what we will do is to create two calculations. One of the two calculations will always yield a NULL. One calculation will only show with a positive % difference (or no difference) and the other will only show with a negative % difference. The calculations will be placed next to each other on the text card (with no spaces) and, as mentioned, only one will be displayed at any given time. This allows us to color the fields separately - the negatives as red and the positives as blue. Here are the calculations:
4 Sales - % Difference - Down // One calc to show if the % difference is going up (or no change) // Another to show if it is going down // One is always NULL // Show both on the card and color-code them each IF [3 Sales - % Difference] < 0 THEN [3 Sales - % Difference] ELSE NULL END
5 Sales - % Difference - Up // One calc to show if the % difference is going up (or no change) // Another to show if it is going down // One is always NULL // Show both on the card and color-code them each IF [3 Sales - % Difference] >= 0 THEN [3 Sales - % Difference] ELSE NULL END
Alright, that is all the calculations we need, let's start by building the simplest piece, the line chart. First, add Order Date to the view and choose the Month (without year) option:
Add SUM(Sales) to Rows. Then place 1 Period on Color (and update the colors to black and gray). Also add 1 Period to the filter shelf and exclude the NA value - then apply that filter to all sheets as we don't care about the NA values. With some formatting and cleanup, it should look like the following:
Okay, the top part is a bit more difficult and requires a bit of explanation. Let's start by adding the SUM(Sales) and the SUM(2 Sales - Previous Value) to the text card. When you do that, it will show the SUM(Sales) but will not show a anything for the Previous Value, like the following:
The reason for it not showing the previous value is that we used a LOOKUP function in this calculation. LOOKUP is a table calculation that pulls the value from the previous row in the view. This view only has one row of data. So we need to break up the total sales by period. So place 1 Period onto Rows. Go ahead and sort Current to the bottom so that the first row shows PREVIOUS and the SECOND row shows CURRENT. It should look like the following:
Wait, my previous value still isn't showing. This is just a problem with how the table calculation is computing. Edit the table calculation and change it to Table (down) as follows:
Once you do this, it should properly show the previous value. Note it will only show a value for the CURRENT period (which reflects the previous period's value) because PREVIOUS does not have a prior row to pull from.
In a moment, we will 4 Sales - % Difference - Down and 5 Sales - % Difference - Up to the text cards, but first, let's create some custom formatting. I always show these percent changes with an arrow showing an increase or a decrease. So instead of showing 5% of -5%, I would show ▲5% or ▼5%. This is much easier for an end user to quickly understand. To do this, you do not need a separate calculation, you can just use some custom formatting.
Multi-select both calculations on the left, right-click, choose Default Properties, and Number Format. In the window, choose percentage and change to zero decimals (or whatever you prefer). Then click Custom on the left. You should see the following:
The formatting in this window is as follows: the first part represents a positive number, the second part represents a negative number, and the third part represents a 0. Each part is separated by a semicolon. So normal formatting would look like this:
0%;-0%;0%
So if we want to use those up arrows and down arrows, we can modify this to be:
▲0%;▼0%;0%
You can copy that from here or you can press/hold ALT and type 30 (for up) or 31 (for down). (Note that I had issues copying from here, so you may need to simply type it into Tableau using the ALT codes above). This is going to format the number so that positive numbers show an up arrow, negatives show a down arrow, and no change will show no arrow.
Now add those two fields to the Text Card and ensure they are set to compute as Table (down).
Okay, we are getting close. Let's do some formatting. I modified the text card to look like the following where sales is large, bold and black, the two calculations for negative and positives are placed next to each other with no space and color-coded as discussed above, then we add a note of previous value and that actual value in gray.
Once you do this, your view should look like the following:
Okay, the values in the row corresponding with CURRENT are exactly what we need. It shows the current value, the percent change, and the previous value. So we now need to get rid of the PREVIOUS value row. So let's use the filter that's on the shelf to simply filter it out. When we do that, we get this:
We've lost our percent change and previous value. Why? Well, these are both table calculations that reference the previous row. But when you filter that value out, it can't calculate it. It's all about the order of operations: Dimension Filters are computed before Table Calculations. So it filters the view then tries to calculate the percent difference and previous value, but that value is long gone, baby. So there are two methods of solving this: 1) Use a table calculation fitler or 2) Hide the data.
Before we do this, set your filter back the way it was so that both the PREVIOUS and CURRENT rows are showing.
Let's first talk about the table calculation filter option. A "table calculation filter" is computed after a table calculation. So if we were able to use a table calc filter to filter out PREVIOUS, then the % change and previous value table calcs will compute first then the table calc filter will compute second. This means the % change and previous values will be displayed even if PREVIOUS doesn't show.
To do this, we are going to trick Tableau and we are going to use a LOOKUP function. Above, we used this to look back one row and in that calculation, we used "-1" to dictate looking back that one row. We are going to do the same thing but look back 0 rows. Looking back zero rows means it's finding the same value. So let's use this calculation (note that you do need to have an aggregation within the lookup, so we will use MIN):
This is exactly the same as a filter on 1 Period, but it's in the form of a table calc filter. Place this field on your filter shelf and check CURRENT only. When done, your view should show the calculations yet filter out PREVIOUS as follows:
That's exactly what we need! We will clean it up later.
The other option is to simply hide the data. This method is easier than the above, but harder to recognize when you are coming back in later to make modifications. To do this, simply right-click on PREVIOUS and choose Hide.
When done, it will look just like the one before:
Again, this is harder to recognize later, so I recommend adding a caption to explain what was done. Note to get it back, right click on the 1 Period field on Rows and choose "Show hidden data".
We ended up at the same result using both methods. Now we just need to clean it up. Right click on 1 Period on Rows and uncheck Show Header. Remove any other headers, grid lines, etc. When done, it should look like this:
The last step is to simply add a vertical container to the dashboard and tile both of these into that container. The end result should look like the following:
I hope this was helpful. Thanks for reading!
No comments: