KNOWLEDGE BASE

Each value can only belong to a single group/category


Published: 04 Dec 2014
Last Modified Date: 19 Nov 2018

Issue

Unable to assign a dimension member to multiple groups/categories.

For example, every customer should be assigned into the categories "low spender" or "big spender" based on the amount of sales. Additionally, some customers should have a second category of "positive profit" based on the amount of profit.

Environment

Tableau Desktop

Resolution

All directions below start from the worksheet "Original" in the example workbook that can be downloaded from the right-hand pane of this article. Directions for creating the worksheet "Original" are embedded in the workbook.

Option 1: Create a Calculation that Returns the Counts

  1. Remove [Customer Name], [Sales], and [Profit] from the view.
  2. Modify [Customer Category] so that all aggregated measures are FIXED to customer name. The final calculation may look like:
    IF { FIXED [Customer Name] : SUM([Profit])} > 0
    THEN "positive profit"
    ELSEIF { FIXED [Customer Name] : SUM([Sales])} > 5000
    THEN "big spender"
    ELSE "low spender"
    END
  3. Create a calculated field with a name like "Customer Category Counts" with a calculation similar to the following:
    IF MIN([Customer Category (fixed)]) = "big spender"
    THEN SUM({ FIXED : COUNTD( IF {INCLUDE [Customer Name]: SUM([Sales])} >= 5000 THEN [Customer Name] END )})
    
    ELSEIF  MIN([Customer Category (fixed)]) = "low spender"
    THEN SUM({ FIXED : COUNTD( IF {INCLUDE [Customer Name]: SUM([Sales])} < 5000 THEN [Customer Name] END )})
    
    ELSEIF  MIN([Customer Category (fixed)]) = "positive profit"
    THEN SUM({ FIXED : COUNTD( IF {INCLUDE [Customer Name]: SUM([Profit])} > 0 THEN [Customer Name] END )})
     
    END
  4. Drag [Customer Category Counts] to the Rows shelf and to Label on the Marks card.

Option 2: Create Multiple Category Calculations

  1. Create a separate calculation for each set of categories that would overlap. In this example a customer is only "low spender" OR "big spender" never both, thus we can combine these two conditions into one calculation. The final calculations may look similar to:
    [Customer Category ~ big/low spender]:
    IF SUM([Sales]) > 5000
    THEN "big spender"
    ELSE "low spender"
    END
    [Customer Category ~ positive profit]:
    IF SUM([Profit]) > 0
    THEN "positive profit"
    END
  2. Replace [Customer Category] with [Customer Category ~ big/low spender] and [Customer Category ~ positive profit]

Option 3: Join to a Master List of Categories

  1. Create a table with a list of all possible categories.
  2. Join the original table to the new list on join calculations of 1=1.
  3. Create a calculated field with a name like "Customer Category w/ Conditions" with a calculation similar to the following:  
    IF [Customer Category] = "big spender"
    AND { FIXED [Customer Name], [Customer Category] : SUM([Sales]) } >= 5000
    THEN "big spender"
     
    ELSEIF [Customer Category] = "profit"
    AND { FIXED [Customer Name], [Customer Category] : SUM([Profit]) } > 0
    THEN "positive profit"
     
    ELSEIF [Customer Category] = "low spender"
    AND { FIXED [Customer Name], [Customer Category] : SUM([Sales]) } < 5000
    THEN "low spender"
     
    END
  4. Replace [Customer Category] with [Customer Category w/ Conditions]

Cause

Tableau Desktop is a read-only program and thus cannot add additional rows to assign one dimension value to multiple groups/categories.

When writing an IF THEN calculation, Tableau Desktop will stop evaluating conditions once one of the conditions is TRUE.

Additional Information

Notes on Option 3:
  • Click the Data Source tab in the example workbook to see how this join is set up
  • If it is not possible to add a master list of categories as a table in the original data source, then for more data sources it is possible to create a text table and use a cross-database join to join the text table to the original table.
  • Step 2 adds a row for every category for every record in the underlying data set. This means that all customers have all three categories.
  • As this method duplicates the data, it is recommended to use to create a separate data source only for this view.
Did this article resolve the issue?