KNOWLEDGE BASE
When Comparing a Member to the Group it Belongs To, Group Level Values are Lower Than Expected
Published: 17 Oct 2016
Last Modified Date: 09 Aug 2017
Issue
When creating a view that compares a member to a group it belongs to, such as in a chart with two bars, then measure values for the group will be lower than expected.
For example when comparing the state "Washington" to the region "West", then SUM( [Sales] ) for "West" will be the total sales for the region minus the sales for "Washington".
Environment
Tableau Desktop
Resolution
Create a calculated field that returns the measure value fixed to the group level for the group partition in the view.
- Create a calculated field with a name like "Header" and a calculation similar to the following:
IF [State] = [State Parameter]
THEN [State]
ELSEIF { FIXED : MIN( IF [State] = [State Parameter] THEN [Regions] END ) } = [Regions]
THEN [Regions]
END
- Create a calculated field with a name like "New Sales" and a calculation similar to the following:
IF MIN([State]) = [State Parameter]
THEN SUM([Sales])
ELSE SUM( { FIXED [Regions] : SUM([Sales])})
END
- Drag [Header] to the Columns shelf.
- Right-click "Null" in the view and select Exclude.
- Drag [New Sales] to Text on the marks card.
Cause
When creating the initial field to return either the state or region, the region group includes all rows except for the selected state as that row as already been assigned to the state group. Rows in the underlying data cannot be assigned to multiple groups in the same calculated field.