Setting Up Formula Calculation

When working with a table one can set up calculation by edited data without saving data to the source. When the setting is enabled, data can be edited without saving to the source.

The setting of calculation by edited and not saved data is used for:

One can enable calculation by edited data in the ribbon:

In regular reports for a data slice on the Slice tab.

In express reports for a sheet on the Data tab.
In dashboards for a block on the Data tab.

The following menu items are available in the Formula Calculation drop-down menu:

For the current sheet/block/slice.
For all sheets/blocks/slices.

NOTE. By default, the setting of formula calculation by edited data is disabled. When the setting is disabled, calculation is executed only after data is saved to the source.

When the For the Current Sheet/Block/Slice menu item is selected, it enables setting of the calculation by edited and not saved data for the current sheet/block/slice.

If the For the Current Sheet/Block/Slice checkbox is not selected for all sheets/blocks/slices, the intermediate state will be selected for the For All Sheets/Blocks/Slices checkbox.

When the For All Sheets/Blocks/Slices menu item is selected, it enables the setting of the calculation by edited and not saved data for all sheets/blocks/slices and the For the Current Sheet/Block/Slice checkbox is automatically selected for all sheets/blocks/slices.

When the For All Sheets/Blocks/Slices checkbox is deselected, the calculation by edited data is automatically disabled for all report sheets/blocks/slices and the For the Current Sheet/Block/Slice checkbox is deselected.

The setting for formula calculation by edited data determined in dashboards affects the added reports too. If calculation by formulas on edited data is enabled in dashboards and data is changed in the table or in the chart, the element formulas are recalculated.

The example of formula calculation is based on the express report. On creating a regular report or a dashboard block, actions will be identical.

To use setting of formula calculation, as in the example, without pre-saving data to express report, follow the steps:

Create an express report, select data source and select elements.

Click the Formula Calculation item on the Data ribbon tab and select the For the Current Sheet item in the drop-down menu.

Select the Column Totals item on the Home ribbon tab, select the Sum item in the drop-down menu. After the step is executed, the table will contain hierarchy totals - Amount (District), level totals - Amount (level) and the grand total. Data from table is summed in the row cells.

Add a new calculated element named Average for the Volga Region for the Territories dimension with formula:

Average({Chuvash Republic[t]},{Perm Krai[t]},{Nizhny Novgorod Oblast[t]},{Orenburg Oblast[t]},{Penza Oblast[t]},{Samara Oblast[t]},{Saratov Oblast[t]}

To edit the formula of dimension calculated element, the Expression Editor dialog box opens. After the step is executed, the Average for the Volga Region new calculated element which calculates average total value for several regions of the Volga Region for all years in the Territory dimension is added.

When data about the Volga Region is changed in region cells there will be a data recalculation by previously set formula in the calculated element. Data in the Sum (District) and Sum (Level) rows will be recalculated by formula according to the made changes.