KNOWLEDGE BASE

Creating Dynamic Calculation to Find Top and Bottom N%


Published: 10 Mar 2014
Last Modified Date: 18 Oct 2017

Question

How to create a dynamic calculation to find the top and bottom N% of values in a view.

Environment

Tableau Desktop

Answer

The following instructions can be reviewed in the workbook attached below.

Step 1: Create Calculated Fields

  1. In Tableau Desktop, connect to Superstore sample data.
  2. Select Analysis > Create Calculated Field.
  3. In the Calculated Field dialog box:
    • Name the calculated field. In the example workbook, the calculated field is named "Index".
    • In the formula field, create a calculated field similar to the following:
      INDEX()
  4. Click OK.
  5. Select Analysis > Create Calculated Field.
  6. In the Calculated Field dialog box:
    • Name the calculated field. In the example workbook, the calculated field is named "Size".
    • In the formula field, create a calculated field similar to the following:
      SIZE()
  7. Click OK.
  8. Select Analysis > Create Calculated Field.
  9. In the Calculated Field dialog box:
    • Name the calculated field. In the example workbook, the calculated field is named "Index/Size".
    • In the formula field, create a calculated field similar to the following:
      [Index] / [Size]
  10. Click OK.
  11. Select Analysis > Create Calculated Field.
  12. In the Calculated Field dialog box:
    • Name the calculated field. In the example workbook, the calculated field is named "Top 30% or Bottom 30%".
    • In the formula field, create a calculated field similar to the following:
      IF [Index/Size] <= .3
      THEN "Top 30%"
      ELSEIF [Index/Size] >= .7
      THEN "Bottom 30%"
      END
  13. Click OK.

Step 2: Create the View

  1. Drag State to the Columns shelf.
  2. Drag Category to the Rows shelf.
  3. Drag Number of Records to Text on the Marks card.
  4. Right-click and drag Number of Records to the Columns shelf, select MIN(Number of Records), and then click OK.
  5. Double-click the x-axis to open the Edit Axis dialog box.
  6. Select Fixed, starting at 0 and ending at 1, and then click OK.

Step 3: Format the Top 30% or Bottom 30% Table Calculation

  1. On Columns, right-click Number of Records, and clear Show Header.
  2. Drag Top 30% or Bottom 30% to Color on the Marks card.
  3. On Color, right-click Top 30% or Bottom 30% and select Edit Table Calculation.
  4. In the Table Calculation dialog box, make the following selections:
    • For Calculated Field, select Index.
    • For Compute Using, select Advanced.
    • In the Advanced dialog box, make the following selections:
      • Move State and Category to Addressing, in that order.
      • For Field, select Number of RecordsSum, and Descending.
      • Click OK.
    • For At the level, select State.
    • For Restarting every, select None.
    • Click OK.
  5. On Color, right-click Top 30% or Bottom 30% and select Edit Table Calculation.
  6. In the Table Calculation dialog box, make the following selections:
    • For Calculated Field, select Size.
    • For Compute Using, select Table (Across).
    • Click OK.

Step 4: Format the Index/Size Table Calculation

  1. Drag Index/Size to Label.
  2. On Label, right-click Index/Size, and then select Edit Table Calculation.
  3. In the Table Calculation dialog box, make the following selections:
    • For Calculated Field, select Index.
    • For Compute Using, select Advanced.
    • In the Advanced dialog box, make the following selections:
      • Move State and Category to Addressing, in that order.
      • For Field, select Number of RecordsSum, and Descending.
      • Click OK.
    • For At the level, select State.
    • For Restarting every, select None.
    • Click OK.
  4. On Label, right-click Index/Size, and select Edit Table Calculation.
  5. In the Table Calculation dialog box, make the following selections:
    • For Calculated Field, select Size.
    • For Compute Using, select Table (Across).
    • Click OK.

Step 5: Complete the View

  1. In the Measures pane, right-click Index/Size, and select Default Properties > Number Format.
  2. Select Percentage, and then click OK.
 
Did this article resolve the issue?