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.

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 calculated depending on the need. For the case of a total, just summing a range won't give the correct value if the grid is drilled into. Instead, consider using an XLCubed Formula - in particular, the XL3Lookup. 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.

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 is the rows are drilled into, and will propagate along columns if columns are drilled.

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.