KNOWLEDGE BASE

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

Published: 12 Feb 2018

### 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

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])} )`

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])} )`