A Calculated Measure is a custom calculation created using a formula or expression within a data model. It allows users to perform advanced calculations based on existing data or measures in their dataset.In the past, old reporting tools could only do basic maths like adding (SUM) and averaging numbers. But that wasn't good enough for organizations with different analysis needs. So, Calculated Measures were created. They let users make their own custom calculations to fit what they need.
Similarly, in ActivityInfo, Calculated Measures help you analyze data in more advanced ways. They're different from calculated fields because they can allow you to combine data from more than one form in a single measure. Here are a few things you can do with Calculated Measures:
- Combine data from different forms into one measure. For example, you can add up the total number of workshops and the total number of training sessions to get a single number.
- Use different types of aggregations together in the same measure. For example, you can divide the sum of teachers by the sum of students to find the student-teacher ratio.
- Aggregate data several times at different levels. For example, you can write a formula that lets you add up the number of beneficiaries at the district level, find the maximum for each activity within the district, and then add up those maximums to get the total at the country level.
Additionally, a Calculated Measure is designed for aggregation purposes. Every Calculated Measure must include an aggregation function, such as SUMX, AVERAGEX, COUNTX, COUNTDISTINCTX, MINX, MAXX, and other relevant table functions supported by ActivityInfo. If you forget to include an aggregation function in the formula of a Calculated Measure, it will cause an error.
Calculated Measures are evaluated on the fly and can change based on user interactions and filters in the report. Unlike calculated fields, they don't have a specific context tied to a form. Instead, we need to specify the expression we want to use, which can be a specific field or a formula that gives a single value.
The Calculated Measure language in ActvityInfo is inspired by PowerBI’s Data Analysis Expressions (DAX) language and the popular dplyr R package. These formulas provide users with the flexibility to define complex calculations based on specific requirements.