Xross Mode / Feature Overview / Calculated Fields
In This Topic
    Calculated Fields
    In This Topic

    The columns in the pivot table are usually aggregated based on fields in the Data app. However, you can create fields (calculated fields) from the existing fields by using formulas.

    Below are the steps to use the calculated field.

    1. Click the Settings tab on the App settings page, and click the krewSheet settings gear icon from Plug-ins to open the krewSheet settings page.
    2. Click the Calculated Fields button on the Data tab of the ribbon area.
    3. The following dialog is displayed.
      Specify the field name and formula and then click Add. For details on the setting values, refer to the Calculated Fields dialog as shown below.
     Calculated Fields Dialog

    The following are the settings for the calculated fields dialog.

    Settings Content
    Name Specify the name of calculated field.
    Formula Set the formula to specify the aggregation method.

    The added calculated fields appear in the field area and can be used just like regular fields.

    In a normal field, subtotal and grand total is displayed as total values for each group, but in calculated fields, they are aggregated according to the content set in the formula as shown in the following example. ("21,392,079 / 36,341,000" instead of "58.8% + 58.8% + 58.9% + 58.9% + 58.9% + 58.9% + 58.9%")

    The following considerations should be taken into account when using the calculated fields:

    • The calculated field is dedicated to Value field only. It cannot be used for Row and Column fields.
    • You cannot specify an aggregation such as "Average" in the calculated field. It is aggregated according to the content set in the formula.
    • Numerical values should be obtained as a result.
    • You cannot use function that specify a cell range.
    • The only fields you can use in formula are Number and Calculated Field. No other field type can be used.
    • If fields are in and out of the same table or in separate tables, they cannot be combined and used in calculated field.
    See Also