KNOWLEDGE BASE

## Calculating Percent of Total for Aggregations by a Dimension, Across that Dimension

Published: 02 Dec 2016

### Question

How to calculate percentages of a total for aggregations by a dimension, across that dimension.

### Environment

Tableau Desktop

Create a calculated field similar to the following:

`​AVG([measure]) / SUM( {EXCLUDE [dimension]: SUM({INCLUDE [dimension]: AVG([measure])})} )`

The above calculation finds the percentage for a given average of [measure] (assuming [dimension] is in the view), out of the sum the averages of [measure] by [dimension].

• Other aggregation functions may be used instead of AVG().
• The SUM() outside of {EXCLUDE} is there to prevent an aggregation mismatch with the numerator.
The above calculation will find the percentage of the SUM([measure]) at the view level of detail across [dimension], but there are circumstances in which the calculation does not work.
• For aggregation functions such as AVG(), MIN(), and MAX(), information is lost when aggregating across a dimension. AVG([measure]) across [dimension] is different from AVG([measure]) for each value of [dimension].
• Although this calculation is responsive to the view level of detail, if [dimension] is part of a dimensional hierarchy and other dimensions from that hierarchy are in the view, the calculation will not function correctly.
• {INCLUDE} and {EXCLUDE} are affected by Dimension Filters.
•  [Measure Names] cannot be used in calculations. Consequently, this calculation cannot be used to find the percentage across [Measure Names].