Adding extra information to cells

As described in the formatting results topic, you can change the appearance of a cell base on a condition. Basic appearances changes the look of a cell, however, there are more advanced appearances that give you information regarding the relative performance of a cell within the context of a group of cells.

To create such an advanced appearance, select one of the preset advanced appearances from the Appearance submenu within the context menu of a member on one of the axes.

Selecting one the presets will apply the appearance to the selected column or row without any additional input required. You can configure all aspects of the appearance before applying it by selecting More... from the presets menu. This will bring up the conditional appearance dialog, which will look like below when an advanced appearance is selected:

This determines how the relative performance of a cell is calculated. The following formulas are available:

% of min-max spread	The minimum value for the cell group is subtracted from the cell's value and the result is expressed as a percentage of the group maximum - group minimum. When using this formula, the lowest value will be 0% and the largest will be 100%.
% of maximum	The cell value is expressed as a percentage of the group maximum. Using this formula, the largest value is always 100%, the lowest is always > 0%.
% of total	The cell value is expressed as a percentage of the group total. Using this formula, the lowest value is always >0% and the largest < 100%.
% deviation from average	For each cell, the deviation, i.e. the difference compared to the group average, is calculated. The cell's value is then expressed as the percentage of the group maximum's deviation (when above average) or as a percentage of the group minimum's deviation (when below average). Using this formula, values can range from -100% to 100%.

This determines the group of cells used when calculating a cell's relative performance. The following options are available:

Column	The cell's performance is relative to all other cell's in the same column.
Column Group	The cell's performance is relative to the other cells in the same column that have the same row parent member (when drilled) and have the same member in an outer hierarchy (if any) on the rows.
Column Level	The cell's performance is relative to other cells in the same column for which the level(s) of the row member(s) is the same as the level(s) of the cell's row member(s).
Row	The cell's performance is relative to all other cell's in the same row.
Row Group	The cell's performance is relative to the other cells in the same row that have the same parent member (when drilled) on the column axis and have the same member in an outer hierarchy (if any) on the columns.
Row Level	The cell's performance is relative to other cells in the same row for which the level(s) of the column member(s) is the same as the level(s) of the cell's column member(s).

In this case, Column Group has been selected. This means that for New South Wales, the performance is compared to Queensland, South Australia and Victoria, as they are all drilled from the same parent member. Australia itself is compared to Canada (and others below), as they all fall within the same outer member, namely Female. The same members that fall under Male will be compared amongst themselves, not with the members that fall under Female.

In this case, Column Level has been selected. This means that for New South Wales, the performance is compared both Female and Male values for Queensland, South Australia and Victoria and Alberta, British Columbia and Ontario, as they are all on the same level, namely Gender/State-Province. Australia itself is compared to Canada (and others below), both Female and Male, as they all fall within the same level, namely Gender.Gender/Customer Geography.Country.

When checked, the relative performance value, i.e. the value that drives the appearance, will be displayed. If Show cell value is not checked, it will be displayed instead of the actual cell value.

The color used for positive relative performance values. Only available when Show relative performance value is checked.

The color used for negative relative performance values. Only available when Show relative performance value is checked and % deviation from average is used.

The precision used for relative performance values. Only available when Show relative performance value is checked.

An icon set appearance adds an icon to the cell that represents the relative performance of the cell.

When defining an icon set appearance, you can choose from a collection of predefined icon styles. Each style will consist of 3, 4 or 5 icons. The thresholds that defines which icon is to be used for a given cell value can also be configured. Just modify the value next to the icon in the conditional appearance dialog as shown below.

You can also modify the icon used for each threshold; click on the icon to show a drop-down list.

If an increase in the cell values means a worse performance, you can click on the Invert button to swap the icons, so the positive icons are associated with low values and the negative ones are associated with high values.

A color range appearance changes the background color of the cell based on the relative performance of the cell.

When defining a color range, you can choose from a two-color range, with start and end colors, and a three color range, that also contains a middle color. For each relative performance value, the corresponding color will be calculated using the defined spectrum.