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:

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.

GridCalcTutorial Initial.PNG

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.

GridCalcTutorial Formula.PNG

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.

GridCalcTutorial SliceDice.PNG

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.

GridCalcTutorial DollarRange.PNG

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).

GridCalcTutorial InsertEnd.PNG

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.

GridCalcTutorial InsertEndDrilled.PNG

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.

GridCalcTutorial XL3LookUp.PNG

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.

GridCalcTutorial XL3LookUpFormula.PNG

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

GridCalcTutorial InsertEndComplete.PNG

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.

GridCalcTutorial Properties.PNG

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.

GridCalcTutorial WithSubtotals.PNG