**Published:**06 Jan 2017

**Last Modified Date:**28 Mar 2018

### Environment

- Tableau Desktop
- Cube Datasources

### Answer

**Best Practices**recommend to create the calculation for bins and counts of their members within the cube itself. This is due to the fact that cubes contain multiple calculation options that are not available to them in Tableau Desktop, primarily aggregations including COUNT().

- The goal of splitting a measure into bins, then getting a count of said bins is available within Tableau Desktop with a cube data source, but it requires creating multiple calculated fields and using table calculations to achieve. The data source also needs at least one row level detail dimension (every row value is unique) that is related to the measure you are creating a bin for.
- The steps below can be reproduced in the attached sample workbook using Excel instead of a cube data source is attached. (
*Note: due to the fact cubes cannot be extracted. Please bear in mind that since the sample has Excel there is an aggregation using SUM() in one of the calculated fields that is not necessary to include for cubes*.)

Step 1: Create the calculated fields

To create the bins and counts of their members in a cube, first create three calculated fields:

- Click
**Analysis**>**Create Calculated Field...** - Name the calculated field (in this example: !1. Bin Calc 1")
- Enter the following formula and click
**OK**:IF [Measure]>5 then "Bin 1" ELSEIF [Measure]<5 AND [Measure]>2 then "Bin 2" ELSEIF [Measure]<2 then "Bin 3" END

- Click
**Analysis**>**Create Calculated Field...** - Name the calculated field (in this example: !2. Count Calc")
- Enter the following formula and click
**OK**: -
If INDEX()=1 then WINDOW_SUM(if [!Calc 1]="Bin 1" then 1 End) Elseif INDEX()=2 then WINDOW_SUM(if [!Calc 1]="Bin 2" then 1 End) Elseif INDEX()=3 then WINDOW_SUM(if [!Calc 1]="Bin 3" then 1 End) END

- Click
**Analysis**>**Create Calculated Field...** - Name the calculated field (in this example: "!3. Label Calc")
- Enter the following formula and click
**OK**: -
If INDEX()=1 then "Label 1" Elseif INDEX()=2 then "Label 2" Elseif INDEX()=3 then "Label 3" END

### Step 2: Build the view

Now that your calculated fields are built, the next step is to arrange them within the view to get the desired counts.- Drag the row level detail dimension onto the
**Detail**shelf. In this example, "Order ID" in Superstore. - Drag the Count calculation (!2. Count Calc) onto
**Text**. - Using the right-click menu on the Count calculation (!2. Count Calc), select
**Compute Using**> select the row level detail dimension placed on "Detail" (in this example, Order ID). - Drag the label calculation (!3. Label Calc) onto Rows. This will create the desired row labels for each of the bins.
- Make sure that the label calculation is also computed using the row level detail dimension, following the same procedure in step 3.

Thank you for providing your feedback on the effectiveness of the article.

Open new Case

Continue Searching

Knowledge Base

Community

Product Help

Training and Tutorials