When Comparing a Member to the Group it Belongs To, Group Level Values are Lower Than Expected

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.