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:
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:
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:
- Create a view that will include the field or fields/measures you want to assess.
- Create a calculated field that will establish the threshold that demarcates success from failure.
- 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.
- Connect to the data source Sample – Superstore.
- Drag Sub-Category from the Data pane to Rows and drag Region to Columns.
- 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:
- 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
- Now click on, OK.
Update the view to use the KPI-specific shape marks:
- On the Marks card, select the option Shape from the drop-down list of views:
- Drag the KPI field to Shape on the Marks card.
- Click the option Shape on the Marks card to open the Edit Shape dialog box.
- From the Select Shape Palette drop-down list, choose KPI.
- Click on Above Benchmark under Select Data Item and then click the green checkmark in the palette.
- 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:
- 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.
- 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: