Last Modified Date: 03 Jan 2020
Environment
Tableau DesktopResolution
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
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
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
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
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.
Additional Information
Discuss this article... Feedback Forum
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials