Results are Not Filtered Despite Condition in the Formula of a LOD Calculation

Issue

When adding a condition expression after FIXED, INCLUDE, EXCLUDE in a level of detail (LOD) calcualtion then the output is not filtered

Environment

Tableau Desktop
Level of detail calculations

Resolution

Move the condition from the dimension declaration to inside the expression after the colon. For example,

{ FIXED [Category], Region = "Central" : AVG( [Sales] ) }

could become

{ FIXED [Category] : AVG( IF Region = "Central" THEN [Sales] END ) }

Cause

LOD expressions will compute an expression (after the colon) for every unique combination of dimension values in the dimension declaration (before the colon).

Thus when [Region] = "Central" is included after FIXED, then Tableau Desktop computes [Region] = "Central" for every record in the database and returns either a TRUE or a FALSE. This creates two groups (TRUE and FALSE). Then Tableau Desktop computes the average of sales for the combination of category and whether it is TRUE or FALSE.

If the view has not been filtered, then the average sales for TRUE will be added to the average sales for FALSE.