Before we create a Pivot table, it is important that we have a well-defined data source. Columns of the table represent the reporting dimensions (dimensions and measures) and before we begin to make the report it would be useful to prepare well all the reporting dimensions. However, it is sometimes necessary to add some new dimension “on the go” and it is therefore useful to know how to use the Calculated Fields and Calculated Items features.
Calculated Items are formulas that consist of a standard Excel functions and values that we get by dragging reporting dimensions into a table (rows or columns). For example, if in the Rows area we have the reporting dimension ITEM NAME, these values will be the names of specific items within a report. Calculated Items are used eg. so that we can add several related articles into a subtotal. We create them by choosing the Fields, Items & Sets menu, and the Calculated Item option in the Analyze ribbon. It is necessary, before starting this option, to click on a row with item labels; otherwise, it will not be active. After launch, a dialog window that consists of two parts opens. In the upper part, there is a field in which we enter the name of the formula (Name) and the formula (Formula). The formula is created by using item names (items) of the selected reporting dimension (fields), which are displayed at the bottom of the window. By clicking Add, we add a formula that will appear among the elements of the report field that we have entered in the row or column…
Calculated Fields have a slightly wider scope and are used to generate formulas that have existing fields (report dimensions) for arguments that can be used in combination with standard Excel functions. We add the calculated field by selecting the Fields, Items & Sets menu, and the Calculated Field option in the Analyze ribbon. A dialog box similar to the previous one will appear, where the name of the formula (Name) and the formula (Formula) are entered at the top. When creating a formula, use the fields shown in the lower part of the window. By clicking Add, we add a formula that will appear as a new reporting dimension. It will automatically be added to the table, and if necessary we can either throw it out or re-insert it.
The calculated fields are very useful, but they are not an all-powerful option. Before you start using them, consider whether it is possible to use them or it is more appropriate to create a new report dimension by adding a column to the source table and entering the desired formula. The characteristic of the calculated fields is that they always summarize the data and can not use any other data aggregation function (Excel allows us to change the formula, but the results remain the same).
Both Calculated items and Calculated fields are removed by choosing a Name combo-list, finding their name, which shows formula itself, and further clicking the Delete button.