Change Relative to Fixed Point

The Change Relative to Fixed Point expression shortcut calculated the percentage difference between each of the nodes and a node of your choice.

Note: The Change Relative to Start expression shortcut is a special case of this shortcut. When your choice is the first node, the result of the Change Relative to Fixed Point and Change Relative to Start will be identical.

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 Change Relative to Fixed Point has been applied on the Value axis.

The following expression is in this case used on the Value axis. This default expression makes a comparison to the first node in the bar chart (Q1 in 2019).

Sum([Sales]) THEN ([Value] / Sum(If(([X.Year]=2019) AND ([X.Quarter]="Q1", [Value],0)) OVER (All([Axis.X])))-1

The settings can be changed to refer to any of the other nodes as fixed point. In the next bar chart, the expression is changed to make comparisons relative to Q3, 2019 instead. (The arrow points to a bar used to explain what is calculated in the expression further down.)

Sum([Sales]) THEN ([Value] / Sum(If(([X.Year]=2019) AND ([X.Quarter]="Q3", [Value],0)) OVER (All([Axis.X])))-1

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 the part following THEN, each node gets divided by the node specified to be the fixed one. This result is then subtracted by 1 to get the percentage.

For example, calculation of Change Relative to Fixed Point for Q1, 2021 (see the arrow in the previous image):

-30%=$350/$500 - 1

(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 "Change Relative to Fixed Point ([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 Number to show the values in numbers rather than as percentage.
Set fixed point	Specify the fixed point against which the other nodes should be compared by typing a Year and a Quarter, or whatever you have on the hierarchy axis.
Set Point from Marked	Retrieves the point to compare against by marking a node in the visualization. (If multiple nodes have been marked, then the first node will be used.)