Grid Calculations

Calculations can be added to any FluenceXL grid and allow for users to create quick calculations that might not be contained in the cube. These calculations can use any standard Excel formula, from simple sums to complex vlookups, as well as FluenceXL formulae and will be automatically propagated to all the other cells in the row or column. Grid calculations can be linked to grid members in order to respect drilling and swapping dimensions.

Videos and Tutorials

Click here for videos showing grid calculations.

This page contains some examples of using grid calculations: Tutorial: Grid Calculations.

Creating and Managing Grid Calculations

The Add Calculation option is available on the Grid's right mouse button pop-up menu. Alternatively, grid calculations can be added from the Row Calculation or Column Calculation menus in the Grid ribbon (visible when selected cell is on grid).

An empty calculation will be added below the current cell. The formula can be typed into any of the cells, and will be propagated to all the other cells in the row or column automatically. Overtyping the title cell (defaults to 'Calc') will give the calculation a title.

Double clicking the title cell opens the Edit Grid Calculation dialog. This can also be accessed from FluenceXL > Calculation > Edit Calculations right click option. In this dialog you can edit the title, number format and calculation position.

Calculation Position

Link to member - position the calculation either before of after a slice in the grid (shown in the members box at the bottom of the dialog). This means that the calculations can be highly dynamic, appearing in the grid wherever the pinned member does, even after the member moves due to drilling or slice and dicing.

Insert at position - fixes the calculation to a certain row/column in the grid (available from Version 8). This is useful, for example, when calculating the difference between two members, irrespective of what members have been selected. The formula will be treated as a literal Excel cell references rather than pinned to particular members in the cube.

Insert at end - insert the calculation at the end of the rows/columns, even when drilling.

Attached to - If a calculation is fixed to a hierarchy, then it will move with the hierarchy if it is swapped between rows and columns. From version 9, it is possible to attach the calculation to an axis so that it remains on that axis even if the grid structure is changed.

Deleting Calculations

You can view, manage and deleting existing calculations in the FluenceXL > Calculation > Manage Calculations dialog (also accessible through the Grid ribbon). A calculation can also be deleted by selecting the FluenceXL > Calculation > Delete Calculation right click option.

Group Members

Users can quickly add custom member groupings into their reporting - these calculations will then available for selection from the member selector across all grids and formulae sharing the same cube connection within the workbook.

To add a grouping, go into the the grid designer and in the member selector select the members you wish to group (holding Ctrl will allow you to select more than one member). Right click and select Group Members. You can then define a name for the grouping, the type of aggregate to be applied and the parent member the calculation will appear under.

• 
• 

The calculation will then appear in the member selector under the defined parent with the defined name. This can then be selected to be used as part of the grid.

If you wish to amend or delete the calculation, select the Custom Calculations tab on the FluenceXL ribbon. This shows all user defined calculations in the workbook which can then be edited or deleted.

Calculated Values on Measures

Within the Measures Hierarchy Editor, you can perform a calculation on a measure by right clicking and selecting 'Calculated value...'. This will open the MDX formula wizard from which you can create various calculations such as a calculation which will show percentage contribution to specific levels in a chosen hierarchy (measure to base).

From Version 9.1, this wizard also includes common time calculations such as Year to Date (YTD), Month to Date (MTD), Rolling Total and Previous Period. See MDX formula wizard.

• 
• 

Pareto Columns (new in v7.2)

The Pareto Principle is often referred to as the 80-20 rule, that 80% of outcomes are attributable to 20% of causes. They are named after Vilfredo Pareto who lived in Italy in the 19thcentury and observed that 80% of the land was owned by 20% of the people. Pareto charts have both bar charts and a line graph where the bars represent individual values and the line represents the cumulative total.

To use Pareto Charts from FluenceXL, within a grid, right-click on the column header to access FluenceXL’s right-click menu, Grid Charts and Add Pareto Analysis.

Take this simple grid showing Reseller Sales for Product Model Categories for Canadian cities:

Right-clicking on All Products to Add Pareto Analysis brings up this window:

Click OK to return to the workbook and you will see that we have a chart showing that the top 9 cities provide some 80% of the sales.

You could also include the rolling total and percentage in your Pareto Chart.

Notice that we now also have some extra columns on the grid showing the cumulative total of all sales, the sales percentage per category and the cumulative percentage.

So that's Pareto Charts - in a nutshell, an easy to use graphical tool which ties directly into dynamic FluenceXL grids.