Today’s blog is all about the min(1) paradigm for KPI charting in Tableau desktop, which has given us a quick win for nearly all customer engagements.
The blog will teach you how you can…
- effectively design advanced KPI charts
- without developing slow tableau workbooks
- and without table calculations
I will do my best to avoid calling this a trick, it’s a native feature and should be utilized because it isn’t hard, however every company I’ve seen using Tableau doesn’t know about the min(1) kpi chart.
The majority of the industry may speak about this as a trick or hack, however this is merely Tableau reacting to a function, a request.
Calling this a hack would be like calling simple math a “hack.”
What is the minimum value of 1?
What is the minimum value of 1?
I bet you 100 out of 100 times it’s 1.
Which makes this a simple logic or algorithm to generating a KPI chart in Tableau, not a trick.
By the end of the blog;
- you will know how to make a KPI charts without having to make multiple visualizations per measure value.
- you will know how to keep it simple and easy to support
- you will understand what we are doing, why it works, and how.
Developing a good understanding of how to keep Tableau simple, will eliminate the unnecessary bloat and server strain.
Keep it simple, use the auto type feature in Tableau, please don’t make a new calculation. If you’re unfamiliar with the new feature that allows you to start typing calculations in a viz adhoc, I will explain how that works below.
Using Min(1) offers an easy way to learn the product!
If you’re new, or think you’re experienced, or think Tableau is just an easy BI product and you don’t need to learn it… Think again buddy. Tableau is advance and if you”re good at making it simple, Tableau will work incredibly fast.
Learning the min(1) solution from end to end will change the way you solve problems. It will teach you Tableau desktop too.
I gravitated to the min(1) solution because it offers a simple playing field for edits, support isn’t a nightmare, and it’s easy to conditionally color.
Open Tableau, build a min(1) bar chart.
These are easy to build in Tableau VS PowerBI. PowerBI does not offer this much flexibility and it’s more complex to build advanced KPI charts in PowerBI VS Tableau.
Okay, min(1), always 1. Build it.
Start typing min(1) – hit ENTER!
Pro TIP: Tableau desktop offers a quick and easy user experience when adding content to the visualization or SHEET.
In a couple places, you can double click and type. Rows, Columns, and here… on the bottom of the marks card.
Once you have your min(1), let’s play with the axis.
Min of 1 is always 1, giving Tableau the ability to draw a 1.
One will always be one, and Tableau plots one the same as it always plots one. Giving us a conditional bucket to fill with aggregates, classifications, or whatever we decide to develop.
As simple as this sounds, is as simple as a dashboard should be.
Learn more about how to make fast tableau calculations, which inevitably means faster analytics.
Making a KPI Chart in Tableau
You have your min(1) bar established.
For the example, let’s use 3.
Open up the axis on each and change the Fixed end to 1. Zero to One will be the end goal.
Click okay, and you will have your simple min(1) mark covering the entire pane.
Complete the next two similarly.
Once completed, let’s add profit, sales, and a count of customers on the last KPI Chart.
Drag profit on your first min(1), sales on your second min(1), and generate a calculated field for the final customer name count. I want to see the distinct count of customer names, to understand the count of customers doing X.
Once you have these measure values on your KPI charts, you should start to see everything forming, and you can see we have some formatting ahead of us now.
Of course profit is the far left, and sales is the middle, but by the time you open this solution again, you will not understand what we are offering the end users. As a part of offering this solution to the end users, we need to make it sensible.
Making the KPI Chart sensible.
Often times, KPI charts come with an explanation, so don’t forget to explain what the number or chart means.
Also pretend your end users are not super technical and can’t read minds.
Do your best to help people make quick decisions, let’s fill in the blanks with sensible labels!
You can click on any of the three, to open the correct mark card.
The next steps are rather self explanatory, fill in the rest of the labels, make the font be subtly bigger, or bold kpi chart will work for some people.
Think SMART in these steps. Don’t spend 5 hours making a KPI chart look a certain way. Don’t spend 10 hours making tooltips look cool. Ask end users for feedback before wasting time.
Open the ALL marks card, change the color to white.
If you’re not playing with the font formatting, you will have the text color automatically update to black. Which helps the obvious white on white from previous screenshots. Tableau is smart this way if you’re keeping it simple. If you clicked around a lot, reset all the formatting, or start a new sheet. Clicking ahead is cool, ctrl+z is infinite.
Cleaning up your KPI charts
Cleaning up the KPI chart is all about “simple clicks” not “hard clicks.”
Hiding headers may hide a label, so let’s discuss.
Hiding headers may help remove the axis, but it may remove the title of your axis too. Play with Tableau!
Some charts work great with the axis as the label, other times it’s more complex, feel free to play with different labeling methods.
I prefer labeling in the text pad editor, per marks card because it offers an endless amount of options. I enjoy formatting each marks card globally with the sheet formatting tools OR twiddle around with the mark level edits. Depends on what the end goals, which I enjoy designing on paper or whiteboard before I begin. Drawing pictures and steps is my method of visually seeing the algorithm.by tyler garrett
Hide headers with a right click on the axis, and uncheck show header.
Center the text on the “all marks” card because it will update across all marks cards! Work smart not hard!
With text aligning in the middle, our KPI chart is starting to form.
These are personal preference changes, let your end users decide.
The key of utilizing the min(1) is the ability to augment Tableau to a kind of style that fits your formatting needs. KPI charts have lots of variations, this particular one gives you full flexibility to a text editor, which gives your as much flexibility as you can write a calculation. The text can be conditionally colored based on logic, and so can the box or min(1) bar in the background.
I prefer the min(1) bar VS a “square mark” sized large because a min(1) bar is predictable and fixable within editing the axis. As you need two marks colored in 1 mark card space, you can use a dual axis or conditionally color the text.
Right click what you want to format.
Right click what you want to format, is what I ask people to repeat when I teach them Tableau desktop, “right click what you want to format.”
Today, we right click the chart. Because… “we right click what we want to format in Tableau!”
We drive to the “lines” because those are pesky and someone in a meeting will likely ask you to remove the boarders!
Boarders, dividers, tomatos, potatoes, try and HIDE these and see if it’s the right path. It’s easy to get frustrated finding the right edit, I’m not saying I haven’t gone mad and turned everything off, only to find the bar chart had boarders turned on.
Sometimes digging around these formatting menus is easier than telling someone the exact right buttons to hit because no one will memorize all of these clicks. You will get better as you use Tableau more, keep clicking!
Open these, click none. Yay!
Notice, we almost have it complete. We are still showing the zero line. Boo!
Removing the zero line, seems like it gets most of it
Instead of wasting your time, be sure to double check a few more things… zero line, axis ruler, maybe your min(1) has a boarder around the bar?
Axis ticks, zero lines, and grid lines..
Tired of clicking yet?!
Luckily at the end, you’re developing your way into a tool that allows anyone to filter this sheet, and give them blimp view aggregates! Might as well never build this again, you can swap out measures easily or build a 3 by 3 with a simple duplication of your sheet.
You now have 3 aggregated measures and a bar to conditionally color + tooltips.
Okay, we have simple KPI charts down. Let’s jump to looking at more advanced functionality and how to do it with copy paste calculations!
Dynamic kpi charting in Tableau, building an on/off flag using dates and parameters.
Duplicate your sheet and let’s begin our Dynamic KPI charting in Tableau! If you don’t already know, we are using the super store sample set of data that comes default with every installation of Tableau.
It’s easy to build these dynamic charts or KPI charts, if you understand the min(1) steps we explained above, and excited about making on/off flags.
Also, if you’re learning, be willing to start thinking outside of the bucket! Read about our date bucket or period over period date solution in our other blog. Because we will use that code to deep dive here and make our KPI chart more flexible and user friendly.
We steal the code from our date buckets to generate 3 different buckets of data.
DATETRUNC('month', [Order Date])>
then "Current Period" //make this 0
DATETRUNC('month', [Order Date])>
then "Previous Period" //make this a 1
else "Filter" //make this a 2
//[Order date] = date
//[x] = parameter
With this new code implemented, you will only need 1 parameter to begin seeing period over period data.
Build a parameter.
- call it x
- data type integer
- current value 1
Drag and drop your new period over period calculation on the visualization. Exclude Filter.
After you exclude filter, you’re left with two distinct periods of time. Which is now set by your parameter.
Show parameter control by right clicking the parameter.
The parameter is an input into your period over period algorithm. Which takes the number of parameters inputted as months.
When you add more numbers to the input, you’re increasing the months. Because we don’t love static solutions, let’s optimize the date bucket solution too!
Let’s dive back into the period over period calculation and make it better for end users. Start with generating another parameter!
Show parameter and open your calculation.
Update “month” with your new parameter d, which stands for date part.
Notice we change the notes on the bottom of the calculation, leaving comments at the bottom or top is a great way to tell other tableau consultants what you did.
We can further simplify our life by removing the “logical looking text values” and cut it back to only an integer! Rename your calc to a single letter to simplify your future development.
At this point, your calculation is rather efficient and utilizing many parameters. When you save calc or hit OKAY, the settings on the filter will change, check out the popup that follows.
We expect an error because the previous filter on “p o p” calculation or period over period calc – was only filtering on a condition. This condition is no longer possible.
The previous condition was excluding the “Filter bucket” from our period over period solution.
Filter 2! And we are now showing 0, as current, and 1 as previous.
This will save a lot of time when generating calculations like;
(if p=0 then (profit) else 0 end) –
(if p=1 then (profit) else 0 end)
VS calculations like…
(if [p o p]=”Current period” then [profit] else 0 end) –
(if [p o p]=”Previous period” then [profit] else 0 end)
And understanding how slow Tableau works with “strings” is a key to making this calculation fast too.
Rename your sheet, call it “KPI-1” and let’s begin discussing how we can use this pop calculation to show dynamic coloring.
Finding the difference or change without Table Calculations
Table calculations are powerful for ad-hoc analytics, but often times can be out smarted with basic logic or SQL. Out smarting the need to use Table calculations means your workbook will be faster. Everything below shows you how to generate the difference or change variation for color variations, including an in-depth look at how to develop the solution yourself.
Having two sets of numbers and two sets of dates generates a difference or change in the data. A trend because we have an archive of data, archive data is like saying “past data, or historical.”
Whether you’re hunting for a percentage difference or a subtraction difference, finding these are relatively easy with dates.
Write two quick calculations.
These calculations offer two simple drag and drop values that represent X amount of days!
Now we can quickly see how these will start to be utilized.
0 = current period
1= previous period
If current period is greater than previous period, is that a negative or a positive? What we are saying here logically is an algorithm to show a HOT or COLD coloring, aka GOOD or BAD.
Sometimes measure values are not all black and white…
It may seem obvious more money is a positive or GREEN, but realistically an up tick or down tick maybe green or red.
It’s always best to ask what end users first.
For sake of this demo/blog, let’s begin using the concept that current period profit being higher than previous period is a green, we should celebrate our successes and use green to share this win across our dashboard but without generating a Table calculation, we need to generate a difference between two values.
Check your work!
Final steps, make a calculation for sum()-sum().
We need to understand what value is a positive or negative.
Do your best to follow along, feel free to use our Live Chat if you’re stuck at any point.
Build another min(1) chart!
Use the parameters and your new difference calculation to build the KPI values!
We will simulate a user experience by dragging and dropping [Container] to see shipping container options on to the visualization.
Notice in our chart, we don’t have any colors showing Green or Red. However we have logic.
If the Profit value is less than zero dollars, the value is negative. If it’s greater than zero dollars the value is positive.
Let’s rename our calculation to save us the pain in future calculations. Rename it to “C” for change.
Now, if C>0 then positive else negative! We don’t want to pretend writing an if statement here is wise, study about boolean calculations and how they are superior to if statements when the time is right.
Generate one final calculation.
You can add this to your visualization and quickly visualize the difference between periods.
- Add GreenRed to your Color Marks card
- you’ll see it populate your visualization
- change the colors to green & red
Maybe you’re familiar with this kind of color, well here’s why I enjoy this kind of KPI charting! With the calculations we have, and logic developed, we can build an advanced color chart within 1 single KPI mark card, without having to do a dual axis.
Using text editor for conditional coloring using simple logic
Using text editor you are given a lot of flexibility in a user friendly space. Trying to explain dual axis to give an up/down arrow can be more confusing and complex to support for new users.
▲ up arrow
▼ down arrow
By generating two more calculations, you can offer two different things to color in your text editor.
Remove the “containers” dimension and drag it to filters. Convert to a single value list, aka radio buttons.
Radio buttons helps us simulate a typical KPI experience. Our end user has a couple filters, charts are likely below, filtering this way in the visualization gives us the ability to debug immediately.
Swap to Jumbo Box. Notice negative background. What if our end user says, “we don’t want a red background.” Or maybe the end user want’s 3 conditional coloring variables and Tableau can only do a dual axis to show two.
Remove the color GreenRed from the visualization. Drag our two new positive and negative calculations with the text arrows. Put those on the Text mark card.
Where is the positive arrow?
The positive arrow missing can be a complex few steps to figure out, you need to find each arrow, color it, and the end user will see green or red.
That’s why I rather enjoy this method. Also, using the container filter, it gives us the ability to see both positive and negative.
This process avoids needing to figure out where the green arrow is located. For what it’s worth, if you’re not familiar with this step, I encourage you to try and build the arrows without these steps.
IF you’re experience, you may enjoy the fact that you’re not having to edit the color marks card, and you can rest assured your workbook will not break as easily as before.
Re-arrange everything according to your “design requirements.”
Okay, now you’re on the way to understanding how you can
- effectively design advanced KPI charts
- without developing slow tableau workbooks
- and without table calculations
Below are a few final remarks about making your min(1) kpi charts easy to maintain in the future. Not advanced, rather more for easy support.
Lots of min(1) becomes hard to support after X many…
Eventually min(1) has a max and it becomes harder to support. Once you get there, these steps will help.
I try to explain to end users,…
Hammer meet nail, hammer meet glass door, hammer meet hand.
Eventually using the same hammer over and over it feels like, “this shoe doesn’t fit.” You can’t take this one trick and make it your life, eventually you will need to swap to a visualization! With that said, here’s the future of min(1)…
Notice the layering of “more fields”… does it ever look like this when you’re developing your KPIs?
Let’s chat about what’s happening in Tableau world.
Before we begin, let’s swap axis.
Unless you want a vertical KPI chart! Go back.
Okay, because we are using sheet level calcs, let’s discuss what happened.
When we double click on columns or rows, and type min(1), we can see we are typing a calculation, that calculation is a SHEET level only calculation. It doesn’t live anywhere else in the workbook, but it does live in the workbook. from an optimization perspective, don’t bother stressing yourself out or testing what’s best, just consider what’s easiest to support…
Moving on. Type //something on your min(1) calc…
This may not seem easy at first, but here’s what happens!
You type //TheNameYouWantToSee (hold shift, then hit enter), it drops you to a new line, and then you can type min(1)… Or you can open your typed calcs by double clicking, and start from the front of the calc.
End goal, we want our field to show up right meow.
By adding the //comment, you can leave a note to help you dig through your min(1)’s.