CASE and IF Statements Containing Level of Detail Expression Return Unexpected Results

Issue

When using a Level of Detail (LOD) Expression in a CASE or IF statement, the returned results are higher than expected.

Environment

Tableau Desktop 9.0 and later versions

Resolution

The following instructions can be reviewed in the attached workbook.

Option 1

Divide the expression within the LOD calculation by SUM( [Number of Records]).
For example:

CASE [Category]
WHEN "Office Supplies"
THEN { INCLUDE [Customer Name]: MAX([Sales]) }
END

Becomes:

CASE [Category]
WHEN "Office Supplies"
THEN { INCLUDE [Customer Name]:MAX([Sales])/ SUM([Number of Records])}
END

Option 2

Move the aggregation inside the CASE or IF statement.
For example:

CASE [Category]
WHEN "Office Supplies"
THEN { INCLUDE [Customer Name]: MAX([Sales]) }
END

Becomes:

CASE MIN( [Category] )
WHEN "Office Supplies"
THEN SUM( { INCLUDE [Customer Name]:MAX([Sales])} )
END

Cause

Level of Detail expressions aggregate in a unique way from other calculations and measures within Tableau Desktop. When Tableau Desktop validates a calculated field, the software runs a verification to determine what type of field is being returned, which ultimately will determine how the field is aggregated in the view.

When the validation check is run (how Tableau Desktop determines if the calculated field is valid or not), Tableau Desktop also determines that the field is not purely a Level of Detail expression, due to the reference to the dimension. Because the field is not validated as a pure Level of Detail expression, the aggregation in the view shows the "unexpected" result.