Today’s blog is about the min(1) paradigm for KPI charting in Tableau desktop, and how to make advanced KPI charts without needing slow table calculations to do the computations for you. Rather we will show you how to utilize tableau features to generate a better KPI solution.
The blog will teach you how to;
effectively design advanced KPI charts
without developing slow tableau workbooks
and without table calculations
When I first learned this, it was called a “trick.” But the more I use it, the more I realize it’s a native feature and should be utilized because it isn’t complicated.
At times developers think they need to generate many visualizations to move forward and float them in a specific way to generate a dashboard, however as data sizes increases enterprises are finding these dashboards to be inefficient.
Making a lot of visualizations to generate at KPI chart is not the right answer because it will slow down your Tableau Workbook! Instead, we have discovered decreasing the amount of visualization is the best path to optimization and easier cycles of support or additional features. The lack of visualizations means you’re able to do more within the same workspace and keep track of less due to it being all consolidated into 1 chart.
A lot of charts means making a lot of data extract requests, a lot of live data requests, a lot of file data requests, a lot of tool tips, a lot of filters,… Filters do a lot of performance degradation, also known as damaging the user experience due to generating technology inefficiently.
If you have ever been in a position where end users need a faster dashboard and the KPI dashboard is moving slow, then you have found a great wiki on building kpi charts in Tableau.
Learning how to make optimized Tableau KPI Dashboards is a path to building an operationalized Tableau solution you can utilize with any data source in the future, when you’re done building this one time, you’re able to easily copy and paste this Tableau win into more workbooks.
This is a screenshot of many KPI charts, on 1 visualization, pause here to see how this is possible.
What is the minimum value of 1? (min(1))
What is the minimum value of 1? What is X when computing min(1)=x?
One.
Finding the minimum of 1 isn’t rocket science, however we need to ensure we understand this logic to take advantage of the functionality in the product. Also, did you know min(1) is faster than max(1) or attr(1).
Finding 1 is important. You can “fix” a value behind the scenes to a static value, this keeps the axis from moving, giving you a conditional background to edit with the color marks card and the text marks card is now available to build conditional text or colors on text or text images.
Number Of Records or the value of 1 in a calculation will handle the generation of your kpi chart.
By the end of the Tableau min1 kpi charts article…
you will know how to make a KPI charts without having to make multiple visualizations per user request
you will know how to keep it simple and easy to support
you will understand what we are doing, why it works, and how to build it
you will have code to copy and paste and screenshots to follow along
Developing a good understanding of how to keep Tableau simple, will eliminate the unnecessary bloat and server strain. By removing unoptimized development, you’re removing a wait time per interaction on your development. Most environments will not be happy with less than split second or second response times, therefor it’s intuitive to understand how to improve the speeds of your workbooks without having to rearchitect the data.
“I recommend as data grows, you begin adding relational theory to your data pipeline, ask your data engineers to work through the atomic pieces of your data, bring the data to a 1nf, 2nf, or 3nf state until the data moves faster or vastly decreases in data sizes. It’s not unusual to aggregate second data into monthly data if your end users only need monthly data.”
Tyler Garret, Founder of Dev3lopcom, llc
Keep it simple when crafting your tableau dashboards, the more simple the development the easier it will be to maintain solutions in the future.
The entire solution is generated to help save time. Also to save time; Try to use the auto type feature in Tableau, try to avoid making a new calculation when you do not need to create 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.
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. Mostly, I enjoy that my complexities turn into calculations, the calculations I can either duplicate or copy paste back into the workbook, understanding the difference between these two or whether they nest into each other based on these single click is a large difference when generating calculations that need to be connected or not connected. Before getting too far in this tutorial, I recommend you understand the differences between duplicating the calculations and copying the calculations, and also understand the product has two spaces this becomes a negative development path or positive development path. The two places are calculations and dashboards/visualizations. Test the right click copy and duplicate as you evolve because it will define your speeds in the product for a long time.”
Tyler Garrett, Founder of Dev3lopcom, llc
Begin building your first min1 kpi bar chart
Thanks for taking the time to start this min1 kpi bar chart tutorial. If you have any questions or get lost in the tutorial, please contact us or leave a comment. Happy to help.
To begin, start your tableau product!
Open a new visualization, and begin building a min(1) bar chart.
a simple bar chart, min1 generates an axis length of 1
Okay, min(1), always 1. Build it.
Start typing min(1) – hit ENTER!
min(1) on sheet2
Not sure how I’m typing in this area? Double click in the rows or columns, or below your marks card. First double click, then you will see an input appear. If you want to harden these and not have them become “viz only static calcs” then you can drag and drop them into the workbook. I like this method because it’s all up front, it names the calculations the name of the code, and it makes life rather fast compared to not understanding this functionality and always building calculations, which leads to always needing to find those calculations, open those calculations, and edit those calculations. Also, when you need to delete the calculation it takes longer because you have to find it in the workbook, and it’s not sitting in the rows bucket because you made a calculation. Based on this paragraph, you should have a better understanding of options and even the best path for you, it’s dependent on what’s fast for you.
In short, lets walk through these features.
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.
marks card is free to double click below the squares. then you move it to the mark square of your preference.
Once you have your min(1), let’s play with the axis, and there’s a few quick things to learn here.
Make 3, hold ctrl+mouse1 drag and drop to duplicate min1 across columns 3 times. Command+mouse1 click drag and drop for MacOs.
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. You’re changing the value of the axis so that you can build a background on your KPI. This background is now easy to conditionally color. If you do a dual axis and make it a shape, now you have a background that can be conditionally colored, and a shape that can be conditionally colored or changed. Welcome to a world of “smart kpi building” and the removal of all the strange multi chart dashboards created due to not knowing these solutions.
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 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.
omg, kpi charts without boarders.
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.
if DATETRUNC('month', [Order Date])> DATEADD('month', -([x]+ datediff('month',{MAX([Order Date])},today())) , TODAY()) then "Current Period" //make this 0 elseif DATETRUNC('month', [Order Date])> DATEADD('month', -([x]*2+ datediff('month',{MAX([Order Date])},today())) , TODAY()) then "Previous Period" //make this a 1 else "Filter" //make this a 2 END //[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
OKAY
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.
show the parameter on the sheet.
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.
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.
Next steps:
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!
using dashboard to verify calculations work.
Final steps, make a calculation for sum([0])-sum([1]).
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!
Bonus points if you generate this KPI-3!showing an increase of 9,679…
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.
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.
omg green
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.
omg red
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.
notice the positive arrow is missing…
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.
awesome
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.”
lovely
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.
outstanding
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.
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)…
lots of min(1) which is what though?
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.
min(1)
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.
making the new line is all it take
By adding the //comment, and a new line, you can leave a note to help you dig through your min(1)’s. A nice trick to avoid “more fields” becoming Min(1) over and over. Never fun to support min(1) over and over in heavy kpi charts!
