1. Manual
  2. Node settings
  3. Calculations and consolidations in KPI's

Consolidation formulas (for KPI nodes)

Here are described the consolidation formulas used for KPI nodes.

  • Quick Links:

    Description

    KPI nodes (indicators, key figures, measurements, as well as risks and checklist questions) have a setting called "Consolidation formulas." These formulas enable different calculations based on the reporting of underlying departments upwards in the organization. It is also possible to distribute a common value to the underlying departments.

    Example: The measurement "Sick Leave" is present in all departments within the organization. Each individual department fills in their own outcomes, and at the top level, an average of what the departments have reported is displayed.

    Consolidation formulas are located under the "Units" tab in a KPI. To display this setting, the "Tracking to be done - On multiple units" option needs to be selected, and units need to be chosen in the tree structure (as shown in the image).

    To the right (2), all measurement columns associated with the KPI are now displayed. In the example above, there are three measurement columns: Actual, Target, and Achievement. For each column, there is a dropdown list where the consolidation formula can be set.

    Depending on whether the measurement columns are set to input numerical values or answer options with colors (often checklist questions), the consolidation formulas will vary.

     

    Consolidation formulas for KPI nodes with numerical values.

    Please note:  (KPI nodes) that have a calculation formula should not have any consolidation formula, as it may cause them to stop functioning correctly (except for the "Consolidate with own value" formula).

    Since the calculations are performed at each level, they will be accurate as long as the column is set to "None" for the consolidation formula.

    The following consolidation formulas are available for measurement columns (KPI nodes) that use numerical values. These formulas are also used for answer options that do not have specified colors:

    • None: No calculation is performed. Each unit in the organization tree, including intermediate levels and the top level, enters its own value. "None" should be used for all measurement columns that contain a calculation formula (with a few exceptions).
    • Sum: The values/numbers entered by the lower-level units in the consolidation chain (as shown in the red boxes in the image) will be summed upwards in the chain, providing a sum at each intermediate level as well as at the top level. The sum will only be displayed when all underlying units have reported their data.
    • Mean value: The values/numbers entered by the lower-level units in the consolidation chain (as shown in the red boxes in the image) will be averaged upwards in the chain, providing an average value at each intermediate level as well as at the top level. The average will only be displayed when all underlying units have reported their data.

      Note: Avoid calculating averages for percentage outcomes. Instead, calculate percentages in a separate column.


    • Sum (Available): Functions similarly to Sum with the difference that the sum is displayed only for the available values.
    • Mean value (Available): Functions similarly to Average with the difference that the average is calculated only for the available values.

      Note: Avoid calculating averages for percentage outcomes. Instead, calculate percentages in a separate column.


    • Mutual value: This means that the value at the top level of the consolidation chain will be propagated down to all units. The underlying units cannot change the value; only the top level can report it. Common value is used, for example, when a target value needs to be the same for all units.
    • Max (Available): The maximum available value reported by the underlying units is propagated upwards in the chain.
    • Min (Available): The minimum available value reported by the underlying units is propagated upwards in the chain.
    It is possible to use some of these formulas when data is reported from all units in the tree

    Exampels

    (1) Actual: Here, an average of what the underlying units have filled in is calculated.

    (2) Target: A common target value was entered at "Municipal Council," and that is what is displayed at all underlying levels.
    (3) Achievement: For the achievement, we use a calculation formula for the KPI node instead of consolidation. No consolidation is needed since the values used for the calculation (Actual and Target) are already consolidated and available at all levels.

    Consolidation formulas for KPI nodes with answer options in color.

    Note: If the answer options do not have any specified colors, the consolidation options for numerical values will be displayed instead.

    This type of KPI nodes and the need for consolidation are commonly used for checklist questions.

    For KPI nodes using answer options as input and having fixed colors associated with them, the following consolidation formulas are explained below. For these formulas, it is important to consider three things:

    1. Should intermediate levels in the organizational tree be able to provide their own answer, or should the answer for that level be consolidated from the underlying units
    2. If intermediate levels want to provide their own answer, should their answer be included in the consolidation when the answers are compiled?
    3. Should the consolidation be displayed for the immediate level below or for all underlying units?

    To illustrate the results for the different options 1 to 6, we will use the following organization that has responded as follows to a question:

    1. INone:
      No consolidation means that no summary is made. All levels can provide their own answer. The result for Level 1 is as follows:
    2. Response options: 
      Only the leaf units, located at the outermost part of the organizational tree, can provide an answer. The consolidation shows how these leaf units have responded, which can be at different levels in the tree. The result for Level 1 is as follows: The answer "Often" comes from "Southern School Area," which is a leaf unit at Level 3.
    3. Response option with dedicated value, compile without dedicated value:

      At all levels, it is possible to provide a separate answer. The consolidation considers the value one level down in the tree, depending on the current level, but the answer for the current level is not included in the consolidation. The result for Level 1 is as follows:


    4. Response option with dedicated value, comile with dedicated value

      At all levels, each unit can provide its own answer. The consolidation considers the value one level down in the organizational tree, depending on the current level, and includes the answer for the current level in the consolidation. The result for Level 1 is as follows: The answer provided by the unit at Level 1 is included in the consolidation.




    5. Response option with own value, compile underlying

      At all levels, each unit can provide its own answer. The consolidation considers the value from all underlying units, but the answer for the current level is not included in the consolidation.

      The result for Level 1 is as follows:




    6. Response option with own value, compile underlying and with own value

      At all levels, each unit has the ability to provide their own answer. The consolidation includes the value from all underlying units, as well as the answer from the current level. The result for Level 1 is as follows: The answer provided by the unit at Level 1 is included in the consolidation.