Create a basic KPI Chart and Calculated Field Formatting in Tableau

17
Aug 2012

Create a basic KPI Chart and Calculated Field Formatting in Tableau

comment icon4 comment(s) |

When you are just starting out with Tableau it will often surprise you how easy it is to replicate tasks that might take hours in Excel but just moments in Tableau.   While formal training is an excellent start to becoming an expert, sometimes you just need to crush out a quick report.  InterWorks recently received the following Tableau question:

 “I need to monitor KPIs. Some measures are % and others are general numbers. How do I get %  and measure numbers all on ONE dashboard?”
 

 This is a common question when people are beginning to replace their old data tools.  Creating  something described above in Excel could take a dozen hours formatting data, combining functions, and formatting the results.  Instead, Tableau can provide gorgeous, flexible, solutions in less than ten minutes.

In the attached Tableau Workbook file, you'll find a basic dashboard and supporting worksheet, along with example crosstab sheets displaying multiple measures.  Everything in the file was created using the sample Superstore Sales data that comes with Tableau.

 

KPI Chart & Calculated Field Formatting

The dashboard itself is just a basic KPI chart with a selectable measure (Profit or Profit Ratio) and a legend.  Creating custom formatting for the shape labels between percentage and currency can be tricky though.  In this case a calculated field had to be created for the label shelf:

 

Calculated Field Label Formatting

 

Since the data source in this instance is an Excel file, the formatting matches Excel's format maps.  (Thanks to Joe Mako via 'DataViz Dude' on Tableau forums)  The same formatting can be accomplished using a STRING function with concatenation, but the above RAWSQL_STR function works better in this scenario since we have both positive and negative Profit. 

Profit Ratio is a calculated field that divides Profit by Sales per row.  You can see the calculation used by right clicking the Profit Ratio measure and selecting “Edit…” from the drop box.  The KPI chart can be duplicated by reviewing the pill locations on the shelves.  The KPI shapes are assigned based on a calculated field “Profit/Profit Ratio” that returns a "Good", "Great", or "Marginal" result depending on the selected measure, either Profit or Profit Ratio.  After placing the Profit/Profit Ratio calculated filed on the Shapes shelf, the shapes are assigned to each returned value (Good, Great, or Marginal) using the following process:

First, place the calculated field on the Shapes shelf on the Marks card:

Marks Card

 

Second, click the drop down arrow in the shapes legend that appears and select "Edit Shapes...":

Shapes Legend

 

Last, select the drop down menu on the right and select the KPI shapes palette.  Click each calculation result then assign a shape from the palette, when you are finished it should look like the following:

Shapes

 

Crosstabs with multiple measures and formatting

Creating the crosstab sheets (the pages that look like Excel pivot tables) is as simple as double clicking dimensions on your data list and then the measures you would like to review.  I created the 'Multiple Measure Crosstab' sheet by double clicking Product Category and Sub-Category then Region directly out of the Dimension window.  Then I added a few Measures by double clicking Order Quantity, Profit, Sales, and my Profit Ratio calculated field.  I moved Profit Ratio to the end of each Region's  section by simply dragging the header to the right of Sales.  After some simple bolding in the formatting options, you have what looks like an Excel Pivot Table in Tableau.  Alternatively you can drag the Measure Names dimension directly to the Filter shelf and specify which Measure Values you would like to include, then drag Measure Values onto whichever shelf you would like.  An example of this method is on the last sheet in the workbook, 'Pane Down Measures'.

 

Hopefully those of you new to KPI charts in Tableau will have a better understanding of what you need  to get started making your own even more elaborate KPI charts!  If not, don't hesitate to reach out to InterWorks with any questions you have.  It should be noted however that the KPI chart in the attached dashboard is not necessarily a good KPI chart, it simply demonstrates the calculation results and label formatting.  For a great piece on KPI chart functionality see InterWorks' own Ben Sullins post on reducing the visual noise in your KPI chart.  Keep sending us your questions and stay tuned for more Tableau tips and tricks!

AttachmentSize
Example.twbx1.2 MB

Comments

August 30, 2012

Carl Brenner

RawSQL_STR

This is very clever Will. Thanks for sharing.

I tried this on my viz, but Tableau claims that rawsql_str is unknown. Is that because I have downloaded all the data and linked it permanently into the viz (thus, there is nothing to rawsql to)?

I was able to play with your viz and it works fine.

August 30, 2012

Will Jones

Data Source

It could depend on several factors, that specific rawsql_str query was written to look at Excel but that shouldn't be an issue. What was your original data source? What do you mean you linked it permanently to the viz? Even if you are using an extract you should be able to create a rawsql query to it.

Why don't you send me your workbook and I'll see if I can sort it out. My email is willdotjonesatinterworksdotcom.

September 4, 2012

Andy Kriebel

Will, which data sources will

Will, which data sources will this work with? I tried it with Oracle, but Tableau doesn't recognize the rawsql_str command.

September 6, 2012

Will Jones

Andy, While you can run pass

Andy,

While you can run pass through SQL queries to Oracle, the syntax will change depending on your data source. The syntax in the query I wrote was for Excel and is a bit more general, but this site should be able to help you query Oracle with a FORMAT type query.

http://www.techonthenet.com/oracle/functions/to_char.php

In this case I believe the "to_char" function and syntax would completely replace "FORMAT", but I don't have an Oracle environment handy to test this in.

Let me know if that helps!

Will Jones

Search