{"id":6726,"date":"2020-11-09T16:36:07","date_gmt":"2020-11-09T11:06:07","guid":{"rendered":"https:\/\/www.h2kinfosys.com\/blog\/?p=6726"},"modified":"2020-11-10T17:00:13","modified_gmt":"2020-11-10T11:30:13","slug":"calculate-z-scores-and-visualize-key-progress-indicators","status":"publish","type":"post","link":"https:\/\/www.h2kinfosys.com\/blog\/calculate-z-scores-and-visualize-key-progress-indicators\/","title":{"rendered":"Calculate Z-scores and Visualize Key Progress Indicators"},"content":{"rendered":"\n

The z-score (or standard score) of observation is the number of standard deviations above or below the population mean.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

How to calculate a z-score in Tableau?<\/strong><\/h2>\n\n\n\n

Step 1:<\/strong> Connect to the Sample – Superstore data source provided with Tableau Desktop<\/a>.<\/p>\n\n\n\n

Step 2:<\/strong> Now create a calculated field to calculate average sales.<\/p>\n\n\n\n

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:<\/p>\n\n\n\n

WINDOW_AVG(SUM([Sales]))<\/p>\n\n\n\n

Step 3:<\/strong> 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:<\/p>\n\n\n\n

WINDOW_STDEVP(SUM([Sales]))<\/p>\n\n\n\n

Step 4:<\/strong> 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:<\/p>\n\n\n\n

(SUM([Sales]) – [Average Sales]) \/ [STDEVP Sales]<\/p>\n\n\n\n

Step 5:<\/strong> Drag the calculation Z-Score from the Data pane to Columns and State to Rows.<\/p>\n\n\n\n

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

\"https:\/\/help.tableau.com\/current\/pro\/desktop\/en-us\/Img\/z-score1.png\"<\/figure><\/div>\n\n\n\n

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.<\/p>\n\n\n\n

Step 6:<\/strong> Click the Z-score field on Columns and select Compute Using > State.<\/p>\n\n\n\n

This will cause the z-scores to be computed on a per-state basis.<\/p>\n\n\n\n

Step 7:<\/strong> Click the Sort Descending icon on the toolbar:<\/p>\n\n\n\n

\"https:\/\/help.tableau.com\/current\/pro\/desktop\/en-us\/Img\/sort_descend.png\"<\/figure>\n\n\n\n

Step 8:<\/strong> Hold down the Ctrl key and drag the Z-score field from Columns to Color.<\/p>\n\n\n\n

Ctrl + Drag to copy field as currently configured to an additional location.<\/p>\n\n\n\n

Step 9:<\/strong> Ctrl + Drag Z-score from Columns once again. This time you need to drop it on Label.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

\"https:\/\/help.tableau.com\/current\/pro\/desktop\/en-us\/Img\/z-score2.png\"<\/figure>\n\n\n\n

Visualize Key Progress Indicators:<\/strong><\/h2>\n\n\n\n

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

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

    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.<\/p>\n\n\n\n

    Create a view, including the field you want to assess:<\/strong><\/h2>\n\n\n\n

    Here, that field is Sales.<\/p>\n\n\n\n

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

      Create a calculated field that will establish the threshold that demarcates success from failure:<\/strong><\/p>\n\n\n\n

      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:<\/li><\/ol>\n\n\n\n

        IF SUM ([Sales]) > 25000 THEN “Above Benchmark” ELSE “Below Benchmark” END<\/p>\n\n\n\n

        1. Now click on, OK.<\/li><\/ol>\n\n\n\n

          Update the view to use the KPI-specific shape marks:<\/strong><\/h2>\n\n\n\n
          1. On the Marks card, select the option Shape from the drop-down list of views:<\/li><\/ol>\n\n\n\n
            \"https:\/\/help.tableau.com\/current\/pro\/desktop\/en-us\/Img\/kpi1.png\"<\/figure><\/div>\n\n\n\n
            1. Drag the KPI field to Shape on the Marks card.<\/li>
            2. Click the option Shape on the Marks card to open the Edit Shape dialog box.<\/li>
            3. From the Select Shape Palette drop-down list, choose KPI.<\/li>
            4. Click on Above Benchmark under Select Data Item and then click the green checkmark in the palette.<\/li>
            5. Click on Below Benchmark under Select Data Item and then click the red cross mark in the palette.<\/li><\/ol>\n\n\n\n

              The dialog box Edit Shape should now look like this:<\/p>\n\n\n\n

              \"https:\/\/help.tableau.com\/current\/pro\/desktop\/en-us\/Img\/kpi2.png\"<\/figure>\n\n\n\n
              1. 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.<\/li>
              2. Drag SUM(Sales) on the Marks card to Detail.<\/li><\/ol>\n\n\n\n
                \"https:\/\/help.tableau.com\/current\/pro\/desktop\/en-us\/Img\/kpi3.png\"<\/figure><\/div>\n\n\n\n

                You now have a completed view that shows how individual products (sub-categories) are performing across all four regions:<\/p>\n\n\n\n

                \"https:\/\/help.tableau.com\/current\/pro\/desktop\/en-us\/Img\/kpi4.png\"<\/figure>\n","protected":false},"excerpt":{"rendered":"

                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 […]<\/p>\n","protected":false},"author":1,"featured_media":6765,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[804],"tags":[],"class_list":["post-6726","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tableau-tutorials"],"_links":{"self":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/6726","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/comments?post=6726"}],"version-history":[{"count":0,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/6726\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media\/6765"}],"wp:attachment":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media?parent=6726"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/categories?post=6726"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/tags?post=6726"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}