KNOWLEDGE BASE

## Each value can only belong to a single group/category

Published: 04 Dec 2014

### 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.

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.

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.