Adding Context to KPIs in Tableau
The most common request I have received from clients using Tableau is to define criteria for a metric most commonly displayed as a KPI. These metrics all by themselves can be impactful, but adding context in the form of a sparkline or change arrows can help provide context to make that visual more effective.
Sparklines are effective in providing context to a metric as it pertains to time. Providing this information to your end users will help them better understand the metric. Here is how to make a couple different ones in Tableau.
Min and Max Indicator
Often when analyzing a data set you will want to know when the min or max of a time series is. This helps gets a feel for if there are any time cycles in your data as well as simply helping you know when the peaks and valleys are with your metric. A sparkline is a great way to do this, and the formula is below.
IF WINDOW_MAX(SUM([KPI])) = SUM([KPI]) OR WINDOW_MIN(SUM([KPI])) = SUM([KPI]) THEN SUM([KPI]) END
First, take time frame and drag to columns. Choose the desired time unit (year/month/day).
Drag your measure field to rows then add your min/max measure field and create a dual axis (always synchronize axis). Set your min/max measure to dot and color appropriately (most of the time different than line color).
Most Recent Indicator
Most Recent: Even if the most recent metric value isn’t a requirement, showing the most recent month’s metric with a sparkline is great from a visual perspective. Often a simple line as your sparkline can seem visually isolated, but if you add a most recent value, this can be beneficial from a visual perspective. It can also help you identify the current time period more easily.
IF ATTR([Date]) = WINDOW_MAX((MAX([Date]))) THEN SUM([KPI])) END
An important note with sparklines is that it is okay to not have the axis set to 0. Click edit axis, then unselect the Include Zero button.
Although most applications of the sparkline utilize line charts, don’t be afraid to try the bar chart as well. The one distinct advantage of having a bar chart as your sparkline is being able to color your bars by a certain set of criteria. Having a dot on your line chart colored differently indicating a sales miss or hit can be less impactful than having a bar chart colored in the same way.
Having a metric alone is great, but letting end users know the Year over Year (YoY) or Month over Month (MoM) change is even more impactful. When displaying a metric change amount is helpful to have a visual indicator of the change. In this case, we use a colored arrow to signify which way the metric changed.
First, create your change metric. If you are calculating YoY Sales change, then create your field Sales YoY = Sales CY – Sales PY. Now create a field that uses unicode text object to signify if your YoY change is positive or negative.
## Arrow Up IF [Metric]>0 THEN "▲" ELSE NULL END ## Arrow Down IF [Metric]>0 THEN NULL ELSE "▼" END
Make sure to create one field for an increase in the metric and one field for a decrease in the metric. The reason being, is when you cry and color each of the unicode shapes, you won’t be able to drag the field to the color field in the marks card without coloring the whole content of the sheet. This way, if the up or down arrow doesn’t apply to your YoY change then there is no shape present. Drag both fields to the Text field in the Marks card and align accordingly.
The end result is a BAN that looks like the image below.