% of Total

The % of Total expression shortcut calculates how many percent of the total each node contributes with.

Example

As starting point, the bar chart below is used. It shows the quarterly sums of sales for three years.

In the next bar chart, the shortcut expression % of Total has been applied on the Value axis. (The arrow points to a bar used to explain what is calculated in the expression further down.)

The following expression is used on the Value axis.

Sum([Sales]) THEN [Value]/ Sum([Value]) OVER (All([Axis.X]))

The first part of the expression specifies that the column [Sales] should be aggregated as a sum. The result of this part goes into the [Value] column to be used in the expression following THEN.

In this latter part of the expression, each node gets divided by the corresponding node the year before, because the number of steps is set to -1. If -1 is changed to -2, each node gets divided by the sum of all nodes.

For example, calculation of the % of Total for Q3, 2020 (see the arrow in the previous image):

8%=$400/($400+$200+$500+$600+$650+$600+@400+$100+$350+$300+$400+$500)

(see the rectangles below)

The column selector in expanded mode

In the installed client, the extended column selector offers controls that make it easy to change various parameters in the expression shortcut:


Display name	Optionally, specify a different display name instead of the default "% of Total ([Column Name])".
Axis	Specify the axis over which to calculate the nodes. Only categorical axes that perform some kind of grouping can show up in the drop-down list. This means that if you only have a grouping on the X-axis, then this is the only axis available, whereas if you also have colored by a categorical column then the Color axis will be available as well, and so on.
Formatting	Change the formatting of the axis. For example, change to Percentage to show the values in percent rather than as decimal numbers.