Include ‘rolled-up’ measures in a Worksheet

This article shows how to add rolled-up measures as columns in a Worksheet slide, and explains the different calculations which can be used to create measures.

‘Rolled-up’ means that values are aggregated through reporting lines to provide measures for:

• each manager based on their direct reports;
• each leader based on their reporting line; and
• the organization based on all positions.

How to add a measure as a column

The example below shows the ‘Salary’ property being used to calculate total compensation.

1. Select the ‘Slide’ Tab.
2. Ensure the ‘Columns’ section is expanded.
3. Select the ‘Add Property’ icon.

4. Search for the property to be used (‘Salary’ in this example).
   1. Select the property with a check mark if the node-level values are to be included as a column.
5. Select the ‘>’ icon for the property.
6. Select the operation required to calculate the measure (‘SUM’ in this example).

• Columns are added with every check-mark selection.
• Click outside the box when your selection is complete.
• Columns can be removed by selecting ‘x’ for the property in the Slide Pane.

As the SUM measure (b, above) is rolled-up, in this example we see:

• The total cost of the organization at the CEO Row
• The cost of the Chief Product Officer’s reporting line
• The cost of the Head of UX/UI’s team
• The individual cost of direct reports to the Head of UX/UI

What are the options for calculating measures?

In the above example, SUM was selected at step 6 to measure total salary. The illustration below shows all the available calculations:

• The worksheet below has been set up with the selections shown above.
• ‘CNTD’ is Count Distinct.  This has been excluded as this is not appropriate when using number properties with multiple different values.  In our example, no insight is provided by knowing the number of different salary values across the workforce.

1. The property value for individual positions.
2. SUM:  Measures the sum total of values.
3. MIN:  Measures the lowest value.
4. MAX:  Measures the highest value.
5. RNG:  Measures the range between the MIN and MAX values.
6. AVG:  Measures the mean average of values.
7. CNT:  Measures the number of nodes included in the roll-up.

Remember:  In addition to your dataset properties, Generated Properties can be used to calculate measures.  For example, ‘Span’ is often used to calculate Average Span of Control. 

TIP:  'CNT' (count) is used to see how many positions are included in roll-ups and subgroups.  This can help avoid falling into the trap of reporting misleading totals based on small groups, and identify if you have any missing values. 

• The example below shows a count of 379 in the rolled-up total for the CEO (all positions ultimately report to the CEO).
• However, the node information bar shows 381 positions in the dataset.

• You can rapidly identify which positions have missing salary values by:
  1. Sorting the property in the spreadsheet by clicking the heading; or
  2. Setting the filter to show only positions with 'blank' salary values.

Related articles: 

• Visualize properties and values in a Worksheet
• Group Worksheet rows to show measures for subgroups
• Exporting raw data, generated properties and calculated measures