How to create a BAN with % change in Tableau in 5 easy steps.
“What’s my latest month’s total? Is that good or bad?
What was it compared to last month?”
Those are probably questions you get asked a lot as a data viz analyst. These questions usually result in a design element called a BAN (big a** number) or a more professional term, the KPI card.
The KPI card typically contains the aggregated latest value, along with its percent change.
The first part is easy. The latter requires a little more hacking in Tableau. And there are sooo many ways to accomplish this. To name a few:
How to Create KPI and Sparklines in a Single Sheet - Andy Kriebel
Tableau Tip: Two ways to create a clear BAN in your title - The Data School
The Current Versus Comparison Index Callout - Ryan Sleeper
However, since I get asked to build these BANs all the time for my clients, I wanted to share how I tackle it. My version requires no LODs or separate “Metric Current” minus “Metric Prior” calculations.
It’s only 5 key steps, with some options to customize the format. So once you’ve done it a few times it becomes pretty quick and easy.
What’s unique here is that I use the “Hide” functionality. Shinichiro Murakami shares the method here on the Tableau forums.
Steps: (example using Superstore)
1) Set up the aggregated value
Drag your date field, “Order Date” to columns. Be sure the field is discrete. (I filtered to Year 2021 for simplicity)
Drag your measure value “Sales” to text
*See that was easy. A major part of your KPI is already done. Now for that pesky percent change
2) Add the percent change table calculation
Control click the SUM(Sales) pill and drag it to Detail in the Marks Card
Right click the new pill and select Quick Table Calculation – Percent Difference
Ensure your table calc is computing using “Table (across)”
Drag your table calculation to Text in the Marks Card
Right click the table calculation and select Format. Change the Default Number to a percentage in the Pane section
*Looks pretty good right? You would think you could just filter the Order Date to the Latest Month and all things would work out. But when we do that, our percent change goes away. This happens because there is no longer a previous value in the view for the table calc to compare to. Whomp whomp.
So here is my method. Note: Remove any month date filtering you applied and go back to your view from step 2, with all months in the view.
3) Latest vs Prior TRUE/FALSE Calculation
Create a new calculated field called “Latest vs Prior"
Depending on the date granularity in your view, you could swap the date part in the DATEADD function with “week”, “quarter”, “year,” etc.
Text Version:
IF MAX([Order Date]) = WINDOW_MAX(MAX([Order Date]))
THEN TRUE
ELSEIF MAX([Order Date]) = DATEADD('month', -1, WINDOW_MAX(MAX([Order Date])))
THEN FALSE
END
Drag your new field in front of the Order Date pill on the Columns shelf
Ensure your table calc is computing using “Table (across)”
*The TRUE clause finds the latest date value in the view, the FALSE statement finds the one date prior, any other dates in the view will be classified as NULL. The Window_Max function allows this method to work even if new data is brought in.
4) Exclude "Null" and Hide "False"
Right click the Null value and select Exclude
Right click the False value and select Hide
*Score! Now the percent change remains in the view!
5) Update any sheet formatting and add to your dashboard
Hide the field names for columns
Uncheck the Show Header options
Remove the Row and Column Dividers
Edit the font sizing and tooltips
And just as an added bonus, change the Mark to Shape in order to Remove the Blue Highlight on select
(Optional) Custom format on % change
Right click the SUM(Sales) table calculation and select format
In the Pane section, click the Numbers dropdown, and select Custom
Type the following into Custom: ▲ 0.0%;▼-0.0%
Adjust the decimal place by adding more zeros after the decimal
(Optional) Custom format and color on % change – to do this you will need two percent difference table calculations. One for positive and one for negative.
To duplicate the table calculations, I find it easiest to control drag the SUM(Sales) table calc to detail, double click into the pill and type “*1”. Drag that new pill back to Text and now we have our two “identical” table calculations.
Right click your original table calc and select format. This will be your positive.
In the Pane section, click the Numbers dropdown, and select Custom
Type the following into Custom: ▲ 0.0%;""
Right click your duplicated table calc and select format. This will be your negative.
In the Pane section, click the Numbers dropdown, and select Custom
Type the following into Custom: "";▼-0.0%
Click the Text tile in the Marks Card and open up the Edit Label dialog box
Be sure there is no spacing between your two table calc fields
Change the text color of your positive and negative values as desired
The Final Result
So now I’m curious… how do you “BAN”?