KNOWLEDGE BASE

Control Percent of Total Level of Detail Calculations Selectively With Dimension Filters


Published: 12 Feb 2018
Last Modified Date: 25 Sep 2019

Question

How to control percent of total LOD (level of detail) calculations with dimension filters. For example, use a filter to affect the numerator without affecting the denominator.

Environment

Tableau Desktop

Answer

The attached workbook (Tableau Desktop 10.5) demonstrates the following two options. Note: ensure to Show/Hide Captions in the workbook.

Option 1

Filtering on [State], create a calculation lite the following:
SUM([Sales]) / ATTR({FIXED : SUM([Sales])})

You can also use a numerator with an INCLUDE or EXCLUDE LOD, since Tableau evaluates dimension filters prior to evaluating Include or Exclude level of detail expressions. 
ATTR( {EXCLUDE [Category]: SUM([Sales])} ) / ATTR( {FIXED : SUM([Sales])} )
 

Option 2

Use a FIXED statement whose dimension declaration contains the dimension being filtered:
{FIXED [State]: SUM([Sales])}
The resultant calculation is:
SUM( {FIXED [State]: SUM([Sales])} ) / ATTR( {FIXED : SUM([Sales])} )

Additional Information

INCLUDE and EXCLUDE level of detail expressions are affected by dimension filters, since they are evaluated after dimension filters.

Filtering a FIXED Level of Detail Expression without adding the filter to context works as follows:

Just as each mark in the view aggregates along its dimension values, a Level of Detail Expression has a result for each combination of dimension values in its Dimension Declaration.

The below calculation will return 50 results if there are 50 states in the data with values for [Sales].

{FIXED [State]: SUM([Sales])}

Although what is inside the FIXED statement's curly braces {} is not affected by a dimension filter, when we add the calculation to the view we give it an aggregation. Because we are aggregating across [State], the [State] filter affects the results.

In general, when a dimension from a FIXED Level of Detail Expression's dimension declaration is filtered, the aggregated results of the FIXED calculation will be affected.

SUM( {FIXED [State]: SUM([Sales])} ) is affected by a [State] filter, and therefore we can use it as our numerator:

SUM( {FIXED [State]: SUM([Sales])} ) / ATTR( {FIXED : SUM([Sales])} )



Discuss this article... Feedback Forum
Did this article resolve the issue?