One-Sheet Solution for Categorical KPI Cards

Recently I was intrigued by Ken Flerlage's LinkedIn post and followed to find his blog posts on KPI cards and his dashboard of categorical KPIs that includes 12 categories and 24 sheets. Besides all the calculations, it takes much manual work to compose the dashboard with 24 sheets. One may risk introducing errors during the process. Ken pointed out that the KPI extensions by Professor Klaus Schulte and Tristan Guillevin can make the design process a lot snappier. Ken, Klaus and Tristan are all esteemed Tableau Visionaries. 

Here I would introduce a single-sheet solution for categorical KPI cards, regardless of the number of categories. The advantage of the approach is that one KPI card design can be easily scaled to many, avoiding manual repetition of copy n paste, table calc setup, container composition etc. It's an automatic design reuse in Tableau, a topic I always like to emphasize.

Here is the resulting dashboard consisting of a single worksheet. The numbers are lightly formatted so that we can see all the digits during QA process. Feel free to interact with the dashboard in Tableau Public. Download it as your curiosity dictates.

My dashboard actually includes all the 17 subcategories. Besides the original dual Category/Sub-Category data model, I also created a single dimension version based solely on Sub-Category as follows. The purpose is to provide two slightly different templates so that people can use according to their specific need. A simple "Replace Data Source" can do the job for them.

2D: Category/Sub-Category

1D: Sub-Category

The same dashboard file also includes Klaus' and Ken's designs as references. 

The single sheet looks like this:

It uses 16 calculations of which all are table calculations.

Among the 16 calculated fields, 4 are about Change Rate. 6 are indexing functions for other calculations. 

KPIs such as Sales This Year, Sales Prev Year and Change Rate are placed above the line chart by dual axis. The relative positions of the KPIs and line chart can be fine-tuned through a parameter Height Adjust.

Handling of Sparse Data

The above chart is for the State of Maine. The data is very sparse.

Some of the sub-categories for certain states may have less data than others. Some data points are missing at certain months and years. In all, we can't expect to have data for every point of interest in time. 

One of the powerful features of Tableau is its densification technology for handling sparse data. It's kind of interpolation but not exactly. It actually densifies (interpolates) with data marks, not values. We can then assign values to those new marks using Window functions. In more exact terms, the densification provides the foundation for interpolation. Tableau Visionary Hall of Fame Jonathan Drummey has written eloquently on the topic. 

Two Approaches to Densification

Table Calculations

Densification is based on table calculations and especially Index() function. All the 6 index formulas are created and used for densification purpose.

Show Missing Dates

When the dates having data are not contiguous, this approach comes handy for help. Right clicking the drop down menu of a date pill, we can see the menu option of Show Missing Values. This will add date marks between the date range endpoints wherever date marks are missing.

For your exploration

I added comments in each formula on table calculation path so that one can explore and replicate what are under the hood.

Debug Densification

Sometimes I found that a densification didn't generate the marks I expected. Then I will turn to another powerful tool called "Duplicate as Crosstab" in the menu by right clicking the tab under a worksheet. It shows information about every data mark in a spreadsheet, very good for debugging.

Conclusion

This post introduces an alternative way to create multiple categorical KPI cards. It makes the design uniform across all the categories, thus reducing possible design errors and lower the effort to replicate the design multiple times.

The key is the use of table calculations. Especially when data is sparse at some categories, we need to use table calculations to densify the data marks.

If you have further questions, leave comments in the comments or in my LinkedIn.