Measure context

Introduction

All formulas in ActivityInfo are evaluated in a context.

When adding a calculated field in ActivityInfo, the formulas are evaluated in the record context. This means that the formula is evaluated, or computed, for each record in the form.

For example, if you have a form with two quantity fields Girls and Boys, you can add a calculated “Total” field that has the formula Girls + Boys.

In this case, the formula is evaluated for each record.

For calculated measures, however, we start without a context. For this reason, we have to explicitly provide both the form id and the field id we want to reference. For example, if we wanted to the total number of boys, we could write:

SUMX(c21geplcowbxdn2, Boys)

This gives the same result as dragging the Boys field to the Measures pane in the Pivot Table designer and selecting the SUM aggregation.

If we want to find the ratio of the number of girls to boys, we can write:

SUMX(c21geplcowbxdn2, Girls) / SUMX(c21geplcowbxdn2, Boys)

The formula above first sums the number of girls, then sums the number of boys, and finally divides the two sums. For the table above, this gives us 50 / 85 = 0.59

Note that this is different from the calculated field in the table above: there we first divide girls by boys. If you add this calculated field to the pivot table, the sum or average of the ratios will be calculated, not the ratio of the sums.

Row context

In the examples above, we aggregated the values of a single field. In other cases, you might want to sum or average a calculation on each row. To aggregate a calculation of one or more fields rather than a single field, you can use the AVERAGEX function:

AVERAGEX(c21geplcowbxdn2, Boys + Girls)

In the formula above, using the AVERAGEX function changes the context, and the expression Boys + Girls is calculated for each record, and then averaged, just like a calculated field.

In fact, the formula with AVERAGEX is equivalent to the following:

AVERAGEX(c21geplcowbxdn2, Total) 

Where Total is a calculated field defined in the Schools form with the formula Boys + Girls.

Filter context

Of course, in most cases we are not just interested in a single number. In our schools example above, we might want to see the gender ratio by province, or by type of schools. If your schools table includes a field for Province and Type, then you might want to create a matrix with gender ratio by Province and Type:

In this case, the calculated measure is evaluated four times, once for each cell. For each cell, we re-evaluate the formal with a new filter context.

When using pivot tables, you can use the user interface to drag and drop fields as row and column dimensions, which create the implicit filter context. However, you can also change the filter context using the CALCULATE() function. The values in the pivot table above would be the same as the following four formulas:

CALCULATE( SUMX(schools, Girls) / SUMX(schools, Boys), 
	schools.Province == "North", 
	schools.Type == 
"Private")

CALCULATE( SUMX(schools, Girls) / SUMX(schools, Boys), 
	schools.Province == "North", 
	schools.Type == 
"Public")

CALCULATE( SUMX(schools, Girls) / SUM(schools, Boys), 
	schools.Province == "South", 
	schools.Type == 
"Private")

CALCULATE( SUMX(schools, Girls) / SUMX(schools, Boys), 
	schools.Province == "South", 
	schools.Type == 
"Public")

You can also use the CALCULATE() function to change the filter context that has already been applied, either by an enclosing CALCULATE expression, or by the user interface.

For example, if we want to find the percentage of our total female students in each type and location of school, we can use the ALL function to clear any inherited filters.

You could write the calculated measure:

SUMX(schools, Girls) / CALCULATE(SUMX(schools, Girls), ALL(schools))

Now, any filters applied in our matrix will apply to the numerator, but will be cleared by the denominator, yielding a table like:

The table above shows us that 40% of female students are found in Public schools in the North, while only 15% of girls in private schools in the South.

Calculating tables

All of the formulas above calculate a single number. In some cases, however, it is necessary to work with whole tables.

For example, you might want to know how many regions have majority female students.

In this case, we can calculate a new table from our Schools form that sums the boys and girls at each Region.

SUMMARIZE(c21geplcowbxdn2, REGION, “GT”, SUM(Girls), “BT”, SUM(Boys)) 

The result of the SUMMARIZE() function is not a single number, but an entire table.

We still need to calculate a single number from this table, however. We can do this by combining the SUMMARIZE function with the SUMX function:

SUMX(
    SUMMARIZE(c21geplcowbxdn2, REGION, “GT”, SUM(Girls), “BT”, SUM(Boys)),
    GT > BT)

The result of this expression is 1.

As table transformations become more complex, highly-nested expressions can become difficult to read. To make your formulas more readable, you can use the pipe operator. The pipe operator takes the result of an expression and passes it as the first argument to another function call. For example, you can rewrite the formula above as:

SUMMARIZE(c21geplcowbxdn2, REGION, “GT”, SUM(Girls), “BT”, SUM(Boys)) |> 
	SUMX(GT > BT)

This formula gives us the number of regions with majority female student populations.

Forms and Tables

In the introduction above, we have referred to “tables” and “columns”. However, you may be most familiar with ActivityInfo “forms”.

While all ActivityInfo “forms” are “tables” for the purpose of calculated measures, not all tables are forms.

ActivityInfo forms have many properties related to data collection, such as validation and relevance rules, in addition to calculated fields whose values are derived from other fields. Forms also have “fields” like section headers and subforms which help organize data entry, but do not actually have a value. ActivityInfo forms are populated by records, which always have an internal identifier (_id) and a full history of changes.

In the context of calculated measures, an ActivityInfo Form is treated as a table, its records become rows, and its fields become columns.