Using an Aggregate Field to Define the Level of Detail in a Table Calculation

Issue

When partitions in a view are created by an aggregate field, such as a table calculation or an IF statement, and another table calculation needs to return different results for each partition, then the aggregate field does not appear in the Compute Using options for the table calculation.

For example, if customers fall into one of three categories, "High", "Medium", or "Low", that are defined by the following calculation:

IF SUM([Sales]) < 500
THEN "low"
ELSEIF SUM([Sales]) < 5000
THEN "Medium"
ELSE "High"
END

Then [Customer Name] must be in the view for this bins to be calculated correctly. However, if [Customer Name] is in the view, then the marks in the view are broken up by [Customer Name], which means we will not have one sales total per category.

Environment

Tableau Desktop

Resolution

Option 1:

Wrap the aggregate calculation in the level of detail (LOD) expression FIXED. FIXED expressions always return non-aggregate results that can be converted into dimensions. The attached example workbook uses the sample data set Superstore to demonstrate the following directions:

Create a calculated field with a name like "FIXED Sales Category" with a calculation similar to the following:
{ FIXED [Customer Name] : [Sales Categories] }
Replace [Sales Categories] with [FIXED Sales Categories] in the view
Remove [Customer Name] from the marks card

Options 2:

Create a calculation that uses the original logic inside and outside of the table calculation. The attached example workbook uses the sample data set Superstore to demonstrate the following directions:

Create a calculated field with a name like "Sales per Sales Category" with a calculation similar to the following:
IF SUM([Sales]) < 500 THEN WINDOW_SUM( IF SUM([Sales]) < 500 THEN SUM([Sales]) END ) ELSEIF SUM([Sales]) < 5000 THEN WINDOW_SUM( IF SUM([Sales]) < 5000 THEN SUM([Sales]) END ) ELSE WINDOW_SUM( IF SUM([Sales]) >= 5000 THEN SUM([Sales]) END ) END
Drag [Sales Categories] to the Rows shelf
Drag [Customer Name] to Detail on the marks card
Drag [Sales per Sales Category] to the Columns shelf
Right-click [Sales per Sales Category] and select Compute Using > Customer Name
Ctrl+drag [Sales per Sales Category] from the Columns shelf to Label on the marks card
Navigate to Analysis > Stack Marks > Off.

Additional Information

The calculation in option 2 may seem redundant, but every IF statement is necessary. The IF statement inside of the WINDOW_SUM() expression limits the data that is summed up in the WINDOW_SUM(). The WINDOW_SUM() result for any one category will show up in every category. Therefore, we need to place the WINDOW_SUM() expression inside of another IF statement that will only return the result we want in the category we want.

Discuss this article... Feedback Forum