Tutorial: Add a calculated measure

The following section describes a Calculated Measure and how to add a Calculated Measure to a Pivot Table. Calculated Measures allow you to write advanced formulas using the formula editor in order to produce more advanced results from your data analysis.You can add a Calculated Measure to derive new numeric data from regular measures and functions.

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 and allows you to perform analysis that would otherwise be impossible.

In this guide, we will be using an existing ActivityInfo template to show you how to add a Calculated Measure to a Pivot Table. In this example, you undertook a series of interventions for participants who have different educational levels. And you now want to find the ratio of female to male participants at these different levels in order to determine how gender equitable your intervention is; you can use the Calculated Measures to derive the ratio of participants.

The following example that would be used for this guide, contains dummy data based on the development assistance for projects templates in the available ActivityInfo templates.

How to add a calculated measure
  • In this example, we create a new database from this development assistance template and to begin, we  navigate to the database, then to registry and to individuals .
Select the Resources
  • After navigating to the registry and selecting the form," individuals"  to be used for the Pivot Table, then select “add pivot table” from the “analyze drop down
  • This will reveal the content of the database that you have selected
  • Next, you will click directly on "+ Add calculated measure”
Add the Calculated Measure
  • The formula editor will appear. With this, you can use a more advanced structure to add a Calculated Measure to your Pivot Table.  
  • In this example, we want to calculate the ratio of female to male participants to ensure that our gender balance is equitable within the programme. For this purpose, we will use the formula editor to define the value that we want by entering the formula in the formula area
  • Under Functions (left-hand side) you can view the Functions available and on the right hand side we will find the forms, fields and records.
  • Under Forms (right-hand side), click on the dropdown next to the form to view the fields and records available.
  • From here you can select the form and records that contains the data you want to calculate
  • In this example, because the gender records that we are using belongs to the “individuals” form, we will navigate to the dropdown, where we will find the desired records.

If you don't see some of the fields or the options, click on the downward arrows to reveal more contents.

  • Then we can now define the expression that will determine what values are to be returned using the formula in the “formula editor”. You can construct a formula using multiple fields that evaluate to a single value for each record.
  • In this example, to find the ratio of female to male participants, we want to sum the total number of “female” and then sum the total number of “male” and thereafter, divide both to calculate the correct ratio.
  • The formula in this example will be:

"SUMX(c7usghwliu8rk251cj,IF(Gender= ="Female",1,0))/

SUMX(c7usghwliu8rk251cj,IF(Gender= ="Male",1,0))"

This is a combination of the SUMX and IF functions that will begin with an argument (a table or another expression) and in this example, the “form ID”. This formula will bring a sum of individuals “IF” the gender equal female, then 1 otherwise zero DIVIDE by the sum of individuals, IF the gender equal male, then 1, otherwise zero.

Note that the 'form ID" is automatically assigned by the system and varies from database to database. So, in your case, the "form ID" will be different from the one in this example.

To determine the ratio, we first have to sum the total female . To do this, start writing your formula in the formula editor;

  • enter "SUMX (".
  • Click on the "form ID", which in this case is "individuals". This will provide you with the form ID "c7usghwliu8rk251cj".
  • Next, type  “IF” (click on gender and type equal to, then click on "female", thereafter type "1”, otherwise, enter "0". This completes the first half of the formula for the female participants. You should have a formula like this ;

"SUMX(c7usghwliu8rk251cj,IF(Gender= ="Female",1,0))"

  • To determine the sum of the male participants, replicate the formula for the female participants and replace "female" with "male".You should have a formula like this;

"SUMX(c7usghwliu8rk251cj,IF(Gender= ="Male",1,0))"

  • Next, to calculate the ratio, divide the formula for the female participants by the formula for the male participants by using “/” symbol in between both formulas. You should now have a formula like this;

"SUMX(c7usghwliu8rk251cj,IF(Gender= ="Female",1,0))/SUMX(c7usghwliu8rk251cj,IF(Gender= ="Male",1,0))".

  • Click on "Done" when the formula is valid. If the formula is valid then the done button will be enabled. Otherwise, it will remain disabled and a pop (in a red dialogue box) up from ActivityInfo will display that "this formula is invalid" and you should check to correct the error.
  • After clicking on “Done” the value will appear.
  • You can add a custom name to your Calculated Measure.

Next, to get the value for the Calculated Measure at different education level, you can also add dimensions in rows. For every dimension that you add, you will get metrics of two cells, implicitly filtered by the dimensions you have included. (In the example, we included education level as the dimension in the Rows).

 

  • You have successfully added a Calculated Measure that shows the ratio of females to males at different educational levels. The Calculated Measure has been added and you can view it in Pivot Table.
  • Generate nuanced analysis by adjusting your formula to create more advanced values. View the available functions in "formulas" and read more about writing formulas.
  • Continue adding more Calculated Measures and dimensions using the same way.
  • Click on "Save report" and give a descriptive name to your Pivot Table. Save your report as a personal report.
  • Start designing the Pivot Table in the way you wish.