Binning Measures

Measures are typically fields containing numeric information such as Sales and Budget. When you place a Measure on the Rows or Columns shelf, it creates an axis. However, sometimes you may want to organize the values of a measure into specific groups. For example, suppose you have a measure that holds the ages of customers ranging from 18 to 90. Rather than break a view down by every age, you may want to analyze based on age groups (18 to 25, 26 to 33, etc.). You can create these ranges by binning the data.

Say you are analyzing the sales performance for a superstore. One way to look at sales is in the form of a histogram so you can see the amount of sales that are less than $500, between $500 and $1000, and so on. To build a histogram, you first have to bin the Sales Total values into categories.

Step 1

In the Measures pane, right-click the measure you are interested in (in this case, Sales) and select Create Bins.

Step 2

In the Create Bins dialog box, specify the size of the bins. In this example, type 500 in the Size of bins text box.

Step 3

Under Range of Values, click Load to load the minimum and maximum values for the field you are binning. This information helps you select a bin size that makes sense for your data.

Step 4

Click OK. A new dimension is created called Sales (bin).

Step 5

Drag the new Sales(bin) dimension to the Rows shelf. Notice that sales values are broken into $500 bins. Each bin label designates the lower limit of the bin's range. The lower limit is inclusive. For example, the bin labeled $1,000 contains numbers equal to or greater than $1,000, but less than $1,500.

Step 6

From the Measures pane, drag Sales to the Columns shelf.

Step 7

Right-click Sales on the Columns shelf and select Measure (Sum) > Count.

Step 8

The final view is shown below. Each bar represents the number of transactions with sales amounts within each bin. We can now see that the majority of transactions at this superstore are for sales less than $500.

Say you examine the diagram above and determine that the values above $8,500 are outliers and should be grouped together. To group them, you can create a calculated field and then create a bin from the calculation.

Step 1

In the Measures pane, right-click the measure you are interested in (in this case, Sales) and select Create Calculated Field.

Step 2

In the Create Calculated Field dialog box, specify the name of the new field. In this example, type Adjusted Sales in the Name text box.

Step 3

In the Formula text box, type the formula to round the outliers to the value you want to use. In this example, type IF [Sales] >= 8500 THEN 8500 ELSE [Sales] END .

Step 4

Click Check Formula to verify that the formula is valid.

Step 5

When finished, click OK.

Step 6

In the Measures pane, right-click Adjusted Sales and select Create Bins.

Step 7

In the Create Bins dialog box, in the Size of bins text box, type 500.

Step 8

Under Range of Values, click Load to check that the bin size is appropriate, and then click OK.

Step 9

From the Dimensions pane, drag Adjusted Sales (bin) on top of Sales (bin) on the Rows shelf.

The diagram shows all sales that are over $8,500 at the $8,500 level.

Note: You cannot use aggregated formulas in bins.