As opposed to a measure whose data is entered manually, a calculated measure uses a mathematical formula to calculate values, based on data from other measures.
- A calculated measure, whose data comes from other measures, is called an aggregated measure (or parent measure).
- A measure whose data is used for the calculations in a calculated measure is called a disaggregated measure (or child measure)
Note: This article discusses how to create a calculated measure whose data comes from other measures. If your account includes variables, you can also create a calculated measure whose data comes from one or more variables.
In this example, the indicator “% of students who graduate high school on time,” will use calculated data.
This calculation requires data from two disaggregated (child) measures:
- Total number of high school seniors
- Total number of high school seniors who graduate on time.
The two disaggregated measures in this example are already created, even though they are not directly included in any scorecard container. (This is a common use case for creating measures from scratch, not part of a container.)
Note: This example demonstrates how to create a parent measure when child measures are created in advance. While this is the more common way to create an aggregated measure, you can work the opposite way: linking child measures to a parent measure already set up.
Keep in mind that each child measure must have the same frequency as the calculated (parent) measure.
This example uses a custom frequency called “Graduation,” an annual frequency with data recorded every June 25. All child measures must also contain data.
Open the parent measure that will use calculated data. Again, this parent measure must have the same frequency as its child measures. By default, measures are Not Calculated. To change this, click the measure’s Edit button.
Open Data Properties and set the Calculation Type, which for this example is Ratio x 100 because the desired result is a percentage. (This is also where you could change the Frequency, if needed.) If the calculated data is to be compared to target values, be sure to enable Manual Targets. Click Save.
Note: The calculation types of Sum, Average, and Ratio are self-explanatory, and are performed on all disaggregated measures added to the measure. There are also several types of accumulated measures XEF that are performed on only one disaggregated measure.
If you need to use a calculation other than the provided defaults, you can create a custom calculation.
The calculation type is now listed in the measure description. To define a calculation data source, click Add Existing Measure in the Relationships section. (Or use the Add Variable button if your data source is a variable.)
In the Add Measure(s) To window, set the Relationship to Disaggregated - Child. This means the measures you’re about to add will provide the source data for the calculation.
To find the child measures, you can filter by type, tags, or title. Click Search to see the results. Only measures that have the same frequency as the calculated measure will be available for selection.
Note: If you have access to shared measures from other organizations, those measures can be used for calculations. Use the Instance drop-down at the top to look for measures from a specific organization.
When selecting the child measures that will be used for the calculation, keep in mind that for some calculations, selection order is important. In this example, because the calculation is a ratio, the measure for graduates must be selected before the measure for total students. (Though if you get the order wrong, it can be easily fixed later.) Click Add to continue.
The percent calculation in this example requires two measures, and two measures were added. This automatically executes the calculation. (If only one measure were selected, calculations would not be completed until a second measure is added.)
After the system completes the calculations, the graph for the calculated measure is generated with the calculated values. If the data looks incorrect, or you want to add target values, click Edit Data.
The child measures are listed in the Data section, and you can drag to rearrange their order if needed. You can also add target values here, if enabled. Click Save when finished.
Note: When values are calculated, they can not be overwritten manually.
In the Relationships section of the calculated measure, the child measures appear under Disaggregated Measures that are used to Calculate this Measure. Child measure order can be rearranged here as well, though you would have to edit the data again and save, in order to recalculate. Child data can be edited by opening either child measure individually, but you can also edit all child measures at once, by clicking Edit All Disaggregated Measure Data.
This opens the calculated measure, showing Current Data at the top and each disaggregated measure below. Each disaggregated measure can be opened individually by clicking its name. Clicking the Edit button opens all child measure data tables in one view, where you can make edits and save.
When a disaggregated measure is open, its Relationship section shows the calculated measure that uses it, listed under Measures that Aggregate this Measure.
In the scorecard view, calculated measures are indicated by a green dot. If data is missing so that the values cannot be calculated, this dot will be red.
Note: Many users create nested calculations. For example, you could create “Calculated Measure 1” for the sum of high school seniors from multiple schools. “Calculated Measure 2” could produce the sum of on-time graduates from the same schools. Then “Calculated Measure 3” could produce the percent of all on-time graduates, based on data from the previous two calculated measures. The advantage of nested calculations is that data editing need only be done to the original source measure.