Loading

Each value can only belong to a single group/category

Дата публикации: Jun 4, 2025
Описание
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.

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.
Решение
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]
Дополнительные ресурсы
Notes on Option 1:
  • This solution requires that the underlying data contain at least one row for each category. Further each category must have a row that does NOT meet the criteria for any categories listed previously in the calculation. For example, In order for "low spender" to show up in the view, there must be at least one row that is a low spender and is NOT "positive profit" or "high spender".
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.

Номер статьи базы знаний

001474113

Вложения

group values into multiple groups.twbx

2465 KB

 
Загрузка
Salesforce Help | Article