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

Published: 02 Dec 2016
Last Modified Date: 20 Jul 2023

Question

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

Environment

Tableau Desktop

Answer

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].

Additional Information

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].