Last Modified Date: 09 Dec 2022
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.
Additional Information
Create Bins from a Continuous Measure
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