Example: Using Grouping with formulas that reference multi-member rows or columns

You can use grouping to execute formulas against a single row or column with multiple members selected, generating a formula result for each member grouping. The multiple members can either be several individually selected members in a single row or column, or a dynamic member selection function, such as "Children" or "Descendants". A common use case for this is either Period or Scenario variance formulas in the columns with another dimension with multiple members selected.

For example, in a grid with the Entity and Scenario dimensions in a single column, where the Scenario dimension has "Actual" and "Plan" selected and the Entity dimension that can have multiple members selected, the requirement is to display a grid variance formula column for each Entity member.

In the below grid design, with Accounts, "Net Income" selected, in the rows, Scenarios and Entities in the columns, Column A has:

• Entities: "Children of Current POV", which will display the children of the currently selected Entities member in the POV. This will result in multiple columns being returned for Entities.
• Scenarios: "Actual" and "Plan".

Here is a preview of the grid output with "Total Entities" select for the Entities POV:

To accomplish having an "Actual vs Budget" variance display for each Entity member, insert a formula column to calculate the variance using expanded member references, to refer to the Actual and Plan member, which are both in Column A:

VARIANCE ([A(A)], [A(B)])

A custom heading of "Variance" was also added in the formula columns in the Scenario row.

To add the grouping and apply some formatting:

• To separate each Entity’s Actual, Plan and Variance columns, a Separator column is inserted to the right of the Variance formula Column B.

• To display the same Entity name across the Actual, Plan and Variance columns, select the Entity cells for columns A and B, right-click and then select Merge Cells.

• With Columns A, B, and C selected, create a Grouping on the Entities dimension.

Here is a preview of the resulting grid:

The results will adjust if a different Entity member is selected in the POV: