Calculate Z-scores and Visualize Key Progress Indicators

The z-score (or standard score) of observation is the number of standard deviations above or below the population mean.

For calculating a z-score, you should know the population mean and the population standard deviation. In cases where it’s not possible to measure every observation of a population, you can simply estimate the standard deviation by using a random sample.

As a general rule, z-scores always lower than -1.96 or higher than 1.96 is considered unusual and interesting. That is, they are statistically significant outliers.

How to calculate a z-score in Tableau?

Step 1: Connect to the Sample – Superstore data source provided with Tableau Desktop.

Step 2: Now create a calculated field to calculate average sales.

Select Analysis > Create Calculated Field to open the calculation editor. Name that calculation as Average Sales and type or paste the following in the formula  space:

WINDOW_AVG(SUM([Sales]))

Step 3: Create another calculated field for calculating the standard deviation. Name that calculation as STDEVP Sales and type or paste the following in the formula space:

WINDOW_STDEVP(SUM([Sales]))

Step 4: Create one more calculated field. This one is required to calculate the z-score. Name that calculation as Z-score and type or paste the following in the formula space:

(SUM([Sales]) – [Average Sales]) / [STDEVP Sales]

Step 5: Drag the calculation Z-Score from the Data pane to Columns and State to Rows.

You will notice that the Z-score field on Columns has a table calculation icon on the right side (i.e., a small triangle):

The calculation STDEVP Sales function is based on the WINDOW_STDEVP function, which is a table calculation function. In turn, the Z-Score function is a table calculation function because it includes STDEVP Sales. When you are using a calculated field that includes a table calculation function in a view, it is the same as adding a table calculation to a field manually. You can also edit the field as a table calculation.

Step 6: Click the Z-score field on Columns and select Compute Using > State.

This will cause the z-scores to be computed on a per-state basis.

Step 7: Click the Sort Descending icon on the toolbar:

Step 8: Hold down the Ctrl key and drag the Z-score field from Columns to Color.

Ctrl + Drag to copy field as currently configured to an additional location.

Step 9: Ctrl + Drag Z-score from Columns once again. This time you need to drop it on Label.

You will now have a distribution of z-scores broken out by the state. California and New York both having z-scores greater than 1.96. You can now conclude from this that California and New York have significantly higher average sales than other states.

Visualize Key Progress Indicators:

A Key Performance Indicator (KPI) is a measurable value that shows how effectively a company is achieving its key business objectives. At a high level, the procedure requires:

1. Create a view that will include the field or fields/measures you want to assess.
2. Create a calculated field that will establish the threshold that demarcates success from failure.
3. Update the view to use KPI-specific shape marks to show which values are above the threshold and which are below.

The scenario is using the Sample – Superstore data source provided to show how to build a KPI view that displays a green checkmark for any sales figure over $25,000 and a red cross mark for any sales figure under $25,000.

Create a view, including the field you want to assess:

Here, that field is Sales.

1. Connect to the data source Sample – Superstore.
2. Drag Sub-Category from the Data pane to Rows and drag Region to Columns.
3. Drag Sales from the Data pane to Text on the Marks card.

Create a calculated field that will establish the threshold that demarcates success from failure:

1. In the Analysis menu, select the option Create Calculated Field to open the calculation editor. Name that calculation as KPI and type or paste the following:

IF SUM ([Sales]) > 25000 THEN “Above Benchmark” ELSE “Below Benchmark” END

2. Now click on, OK.

Update the view to use the KPI-specific shape marks:

1. On the Marks card, select the option Shape from the drop-down list of views:

2. Drag the KPI field to Shape on the Marks card.
3. Click the option Shape on the Marks card to open the Edit Shape dialog box.
4. From the Select Shape Palette drop-down list, choose KPI.
5. Click on Above Benchmark under Select Data Item and then click the green checkmark in the palette.
6. Click on Below Benchmark under Select Data Item and then click the red cross mark in the palette.

The dialog box Edit Shape should now look like this:

7. Click on OK to close the Edit Shape dialog box. The shapes in the view show the correct indicators. Now you need to hide the sales numbers.
8. Drag SUM(Sales) on the Marks card to Detail.

You now have a completed view that shows how individual products (sub-categories) are performing across all four regions: