KNOWLEDGE BASE

Manually Choosing the Number of Bins Within a Certain Data Set


Published: 22 Dec 2016
Last Modified Date: 03 Jan 2017

Question

How to define the number of bins with an equal (with a remainder) number of items in each bin.

Environment

Tableau Desktop

Answer

Step 1: Create a calculated field for Index to display a rank from 1 on up

  1. Select Analysis > Create Calculated Field
  2. In the Name text box, type "Index".
  3. In the Formula text box, type:
    INDEX()
    
  4. When finished, click OK.

Step 2: Create a  parameter for the number of bins you want to split your chosen dimension by

  1. In the Data window, click the drop-down arrow at the top right of Dimensions, and then select Create > Parameter
  2. In the Name text box, type "Number of Bins".
  3. For Data type, select Integer.
  4. In the Current value text box, type a number (for example, 50).
  5. Select Range.
    • Select Minimum and Maximum (for example, from 1 to 100)
  6. When finished, click OK.
Note: Because the number of bins will be an integer, choose Integer. You may want to either select a List (if you have specific numbers of bins) or Range (if you would like more flexibility in deciding how many bins to create).

Step 3: Create a calculated field for defining the size of the bins

  1. Select Analysis > Create Calculated Field.
  2. In the Name text box, type "Size of Each Bin".
  3. In the Formula text box, type the formula below, and then click OK.
    IF ([Number of Bins]/SIZE()>.5) AND (([Number of Bins]-1)/SIZE()<.5)
    THEN 2
    ELSEIF [Number of Bins]/SIZE()>.5
    THEN 1
    ELSEIF ROUND((SIZE()/[Number of Bins])-10^(-10), 0) !=INT(SIZE()/[Number of Bins]) THEN
    INT((SIZE()/([Number of Bins])))+ 1
    ELSE
    INT((SIZE()/([Number of Bins])))
    END
    

Step 4: Create a calculated field for Index to determine the correct number of bins to use for each number of items chosen

  1. Select Analysis > Create Calculated Field.
  2. In the Name text box, type" Dynamic Bins".
  3. In the Formula text box, type the formula below, and then click OK.
    IF INT(([Index]-.1)/[Size of Each Bin]) = [Number of Bins] THEN
    INT(([Index]-.1)/[Size of Each Bin])
    ELSEIF INT(([Index]-.1)/[Size of Each Bin]) < [Number of Bins] THEN
    INT(([Index]-.1)/[Size of Each Bin])+1
    ELSE
    [Number of Bins]
    END
    

Step 5: Build the View 

  1. Using the Superstore sample data source, drag a measure to Text.
  2. Drag a dimension to Rows.
  3. Drag the "Index", and "Size of Each Bin" calculated fields to Detail.
  4. Right-click the parameter in the Data window and select Show Parameter Control. The parameter control is now added to the sheet and is ready for customization.
  5. Right-click the Dynamic Bins field, select Discrete, and then place it in front of the existing field on the Rows shelf.
This also works for graphs. To create a bar chart using the view you created above, simply drag the Sales field from Text to the Columns shelf. An example can be found in the workbook "Choosing the number of bins" attached to this article. 

Additional Information

To view these steps in action, see the video below: 

 
Did this article resolve the issue?