Tutorial: Grid Calculations

You will often find the need to add a calculation based on a grid that isn't contained within the cube data. XLCubed provides various methods for adding calculations, including:

• Grid Calculations
• Grouping members
• MDX Calculations
• MDX formula wizard

In this tutorial we will look at a few examples of creating calculations within a grid.

Difference Between Two members

Consider the grid below which shows the Reseller Sales Amount across various Calendar Quarters. We will add a calculation which shows the difference between the values in Q3 and Q4 in 2017.

Select the Q4 CY 2017 column header. In the XLCubed Grid ribbon, select Column Calculation (or use the right-click menu XlCubed > Add Calculation).

A new column will appear to the right of the currently selected column. Overtype the default 'Calc' title to the desired title. In any of the value cells in the calculation column, type the relevant formula. In this case, F7 - G7.

This formula is then propagated down all rows, even when drilling. If the position of the hierarchies is changed, the calculation remains pinned to the Q4 member. In the example below, the Date hierarchy has now been moved on to rows. The 'Change' calculation appears wherever Q4 CY 2017 does.

If the Q4 CY 2017 member no longer appeared in the grid (due to a slicer change,for example), then the calculation would also not be visible.

Referencing other rows

Cell references to cells inside the grid will be converted to a reference to the relevant column member. The reference is to that column, not the row member.

If you need to reference another row you can do so by "dollaring" the cell reference, in which case the reference will be an Excel range reference and be filled according to the standard Excel rules.

This example shows a delta calculation, in the second grid the reference is dollared so that it references the row below the current cell.

Fixed Position Totals

We may like to show a calculation that is always fixed to the last member in the grid regardless of what members have been selected, i.e. is always the last row/column.

Using Fixed Position Grid Calculations

To do this, create a row calculation using the same method as before.

Double click on the calculation title to open the Edit Grid Calculation Dialog. Select 'Insert at End'. Here you can choose whether the calculation should be attached to the hierarchy or the axis (see Calculation Positions).

The calculation now appears at the end of the Geography and Reseller hierarchies. If a member is drilled into (in this case, Canada), the calculation stays fixed to the bottom of the rows.

Any formula can be used in this calculation depending on the need. For the case of a total, just summing a regular excel range won't give the correct value if the grid is drilled into. Instead, there are a couple of approaches to consider:

Retrieve the value for the 'All' member with an XL3Lookup

Consider using an XLCubed Formula - in particular, the XL3Lookup, to return the 'All member'. This formula has a helpful wizard which can be access through the XLCubed Ribbon > Insert Formula > Value.

Select the 'All' member for the Reseller and Geography hierarchies. The calendar selection will be dependant on the column, so make sure the formula uses the correct dollaring.

Calculate the sum over each column in a named range

A different approach would be to create a named range for the grid and sum across each row/column. See this page for details: Named Ranges.

Apply some formatting to the calculation to create a finished report:

Using 'Fill Formula Next to Grid'

It is possible to create a similar effect without adding an XLCubed Grid Calculation. Write the relevant formulae in the cells underneath the grid. In the grid properties, turn on shifting rows and fill formulae next to grid (Grid Properties). This will ensure the formula is not overwritten if the grid rows are drilled into (rows are shifted), and will propagate along columns if columns are drilled (formula is filled next to grid).

This technique has been combined with XLCubed Subtotals to create a grid broken down in to subtotals with a grand total in the final row. The grand total is the XL3Lookup formula as used in the previous example.