Manually Choosing the Number of Bins Within a Certain Data Set

Answer

CLICK TO EXPAND STEPS

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

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

CLICK TO EXPAND STEPS

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

In the Data window, click the drop-down arrow at the top right of Dimensions, and then select Create > Parameter.
In the Name text box, type "Number of Bins".
For Data type, select Integer.
In the Current value text box, type a number (for example, 50).
Select Range.
- Select Minimum and Maximum (for example, from 1 to 100)
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).

CLICK TO EXPAND STEPS

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

Select Analysis > Create Calculated Field.
In the Name text box, type "Size of Each Bin".

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

CLICK TO EXPAND STEPS

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

Select Analysis > Create Calculated Field.
In the Name text box, type" Dynamic Bins".

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

CLICK TO EXPAND STEPS

Step 5: Build the View

Using the Superstore sample data source, drag a measure to Text.
Drag a dimension to Rows.
Drag the "Index", and "Size of Each Bin" calculated fields to Detail.
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.
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.

All Support