Redefining Calculation Contexts

The calculation context is the set of data that a calculation uses to generate a result.

The value of a measure depends on the dimensions used in its calculation.

A calculation has both an input context and an output context. You can specify neither, either one, or both.

• The input context is the set of dimensions used to perform the calculation.
• The output context is a set of dimensions that acts as a break in the calculation. It causes the calculation to return values as if they appear in the footer of a table break.

Defining which dimensions are used to generate a measure is called setting the calculation context.

Input Calculation Context: Forcing

The input context for a measure or formula is the list of dimensions that you use in the calculation.

The list of dimensions in the input context appears inside the parentheses of the function that returns the value. You must enclose the list of dimensions in parentheses, even if it contains only one dimension. Separate each dimension with a semicolon.

You have a report that shows sales revenue by state and year, with a section for state.

If you want to display the maximum revenue by state, you can do so by specifying the input context in a formula.

In this case, use the following formula:

=Max([Sales revenue] In ([State];[Year]))

The dimensions in the input context appear inside the parentheses of the function.

The output context makes the formula return a value as if it is in the footer of a block with a break.

The report in the figure Specifying an Output Context shows sales revenue by year and quarter. It includes a break on year and calculates the minimum revenue for each year.

​What if you want to show the minimum revenue by year in a block without a break?

You can do this by setting the output context in a formula.

The formula is:

=Min([Sales revenue]) In ([Year])

The output context appears after the parentheses of the function. Here, the output context calculates the minimum revenue for each year. If you add a column with this formula to the block, the result matches the figure Minimum Sales Revenue by Year.

The Min by Year column shows the minimum revenues that appear in the break footers in the previous report.

In this example, the input context is not set because the default context for the block is year and quarter. The output context decides which revenue by year to return. If you specify both input and output contexts, the full formula is:

=Min([Sales revenue] In ([Year] ; [Quarter])) In ([Year])

This formula first calculates revenues by year and quarter. Then, it returns the smallest revenue for each year.

What happens if you do not set the output context in the Min by Year column?​

In this case, the figures are the same as those in the Sales Revenue column. This is because the default context in a block includes the block's dimensions. The minimum revenue by year and quarter is the same as the revenue by year and quarter, since there is only one revenue for each year and quarter combination.