KNOWLEDGE BASE

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


Published: 13 Oct 2016
Last Modified Date: 11 Aug 2017

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

Answer

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
Did this article resolve the issue?