KNOWLEDGE BASE

Always Include a Value Regardless of Whether the Value is Selected in a Filter

Published: 13 Oct 2016

Question

How to always add the value of a specific dimension to the total even if the dimension is not selected in a filter.

Environment

Tableau Desktop

In this specific example, the three newly created calculated fields can be found in the attached workbook Include Filter Value.twbx to demonstrate the issue.

Calculated Field 1 - Find the static value of the quick filter selection

For example, even if "Arizona" is unselected in a State filter, we still want to include the Sales for Arizona as part of the total in the view. Name the calculated field "_Value of AZ" and enter a formula similar to:
{ FIXED [Category] : SUM(
IF [State] = "Arizona"
THEN ([Sales])
END)}

Calculated Field 2 - Confirm the value is excluded

Find if "Arizona" is selected in the quick filter. It is important to check if Arizona is selected, because if Arizona is already selected, we do not want to add the Arizona Sales to the total again. Name the calculated field "_Count of AZ" and enter a formula similar to:
MAX(IF CONTAINS([State], "Arizona") = TRUE
THEN 1
else 0
END)

Calculated Field 3 - Calculate the final desired value

Name the calculated field "_Sales" and enter a formula similar to:
IF [_Count of AZ] = 1
THEN SUM([Sales])
ELSE SUM([Sales]) + SUM([_Value of AZ])
END