Use this consolidation type to both report numerical values at all departmental levels and aggregate them at higher levels
General
Normally, when numerical values are aggregated in Stratsys, only the lowest-level departments (the underlying units) can report, while the other departments in the tree display the sum or average from the lower levels.
With the calculation formula consolidate with own value, all departments can report outcomes, and an additional column can sum or calculate the average of these values at all levels.
A similar function is also available when using response options instead of numerical values—see this article for more information: Consolidation for response options.
Important: Do not switch from standard consolidation to this consolidation type on existing measures without first consulting Stratsys—there is a high risk that existing data values will become incorrect.
Application
An example could be when you want to report the number of employees per department—employees exist at all levels, not just at the lowest departments in the hierarchy.
You can use this method when all departments need to report a numerical value, and you want to calculate a total, an average, or a maximum value from this data.
Set up the formula
You need two measure columns in your measure for this calculation (unlike standard consolidation, which requires only one column)—this is to enable both reporting and calculation.
The first column is for data entry—this is where departments enter their numerical values. No calculation or consolidation formula should be applied to this column. It can be named anything. Example:
The second column is a calculation and consolidation column—this is where both the calculation and consolidation occur.
Here, you set the calculation formula to Consolidate with own value and specify your outcome column from above:
Save the measure:
Now switch to the Departments tab to configure the consolidation formula—you can use any formula except Common value (which does not calculate, but instead passes a value down through the organization).
Set your desired formula on your calculation and consolidation column.
Your outcome column should not have a consolidation formula—the consolidation is performed in your calculation column. This is an exception, as you normally don’t apply both calculation and consolidation in the same column.
Example with consolidation using Sum:
Below is an example using the consolidation formula sum. The total is calculated at all levels, including the reported value at the current level. The sum for Level 3 (red arrow) is 9: 1 + 6 = 7 from the Level 4 departments, plus 2 from its own level. The total for all departments at the top level is 29, including the 5 employees reported at that level (blue arrow).
Example with Sum, Average, and Maximum value:
Below is an example showing Sum, Average, and Maximum value for the same set of outcomes:
