top of page



Let's "BAN" together!

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:

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]))


ELSEIF MAX([Order Date]) = DATEADD('month', -1, WINDOW_MAX(MAX([Order Date])))



  • 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”?

#Tableau #DataViz #KPIs #BANs


Recent Posts

See All
bottom of page