KNOWLEDGE BASE

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


Published: 02 Dec 2016
Last Modified Date: 02 Dec 2016

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