KNOWLEDGE BASE

Creating Bins and Bount of bin Members using Cube Data Source


Published: 06 Jan 2017
Last Modified Date: 03 Apr 2017

Question

When connecting Tableau Desktop to a cube data source, how to create bins for specific measure amounts, then get counts of members of those bins.

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:
  1. Click Analysis > Create Calculated Field...
  2. Name the calculated field (in this example: !1. Bin Calc 1")
  3. 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
  4. Click Analysis > Create Calculated Field...
  5. Name the calculated field (in this example: !2. Count Calc")
  6. Enter the following formula and click OK:
  7. 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
  8. Click Analysis > Create Calculated Field...
  9. Name the calculated field (in this example: "!3. Label Calc")
  10. Enter the following formula and click OK:
  11. 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.
  1. Drag the row level detail dimension onto the Detail shelf. In this example, "Order ID" in Superstore.
  2. Drag the Count calculation (!2. Count Calc) onto Text.
  3. 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).
  4. Drag the label calculation (!3. Label Calc) onto Rows. This will create the desired row labels for each of the bins.
    1. Make sure that the label calculation is also computed using the row level detail dimension, following the same procedure in step 3.
Did this article resolve the issue?