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