Help Topics

Using Functions in Calculation Expression


The Calculation Editor offers the following types of functions:

Arithmetic Functions

The following table shows the arithmetic functions supported.

Arithmetic Functions

Description

Abs

Returns the absolute value of a number. This can only be used on a single number item such as the result of another calculation or a single member. For example:

Abs(Average(Truffles)
Abs(Truffles)

Average

Returns the average of all the numbers in the definition, which is the sum divided by count. If the count is zero, the average is returned as a missing value.

Count

Returns the count of all numbers in the definition. Missing values are ignored. If there are no values to count, zero is returned.

Max

Returns the highest value in all the numbers in the definition.

Median

Returns the value of the number in the middle of the set; that is, half the numbers have values that are greater than the median, and half have values that are less.

Min

Returns the lowest value in all the numbers in the definition.

Product

Returns the multiplication of all the values in the definition.

Round

Returns the integer part of the number rounded to the nearest whole number. This can only be used on a single number item such as the result of another calculation or a single member. For example:

Round(Sum(gen(2)))
Round(Truffles)

Sqrt

Returns the square root of a number. This can only be used on a single number item such as the result of another calculation or a single member. For example:

Sqrt(Average(gen(3)))
Sqrt(Truffles)

Stdev

Returns the standard deviation of all the numbers in the definition. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

Sum

Returns the addition of all the numbers in the definition. Missing values are ignored. If there are no values to add, zero will be returned.

Var

Returns the variance, which the average squared deviation of each number in the set from the average.

Special Functions

The following table shows special functions supported:

Special Functions

Description

Child

Returns all children of the specified member. For example:

Product: Average = Average(Child(Visual))

will calculate the average of all children of Visual. For Microsoft Analysis Services data sources, you must use the full path to the member names. For example, use:

child([Time].[Calendar].[All Time Periods].[2000])

rather than

child([2000])

Leaf

Returns all leaf-level descendants of the specified member. For example:

Product: Average = Average(Leaf(Visual))

will calculate the average of all leaf members of Visual. For Microsoft Analysis Services data sources, you must use the full path to the member names. For example, use:

leaf([Time].[Calendar].[All Time Periods].[2000])

rather than

leaf([2000]).

Rank

Returns the values from the specified dimensions in ascending or descending order for the specified member. The syntax for Rank is:


Rank(member, dimension, generation, order, grouping)

where:

  • member is the member in this dimension which you want to rank
  • dimension is the dimension on the opposite axis whose members will be used to generate the rank
  • generation is the generation of the members of the dimension to be ranked. A generation of 0 means that all members are ranked.
  • order is either ASC for ascending order or DESC for descending order. In descending order, the largest number will be ranked 1. In ascending order, the smallest number will be ranked 1.
  • grouping is optional, and if it is present and set to GROUPDIM, when there are multiple dimensions on the opposite axis, separate ranking will be done for each grouping of the dimension that is not part of the ranking definition. If it is not set or set to NOGROUP, ranking will be performed across groups. This will only have an effect when there is more than one dimension on the axis.

For example:

Rank(All Products, All Locations, 2, DESC, GROUPDIM)

will add a calculated member ("Rank" will be the name of the calculated member ) based on the values of generation 2 members in the All Locations dimension, with the largest number ranked first and separate ranking within each group in the dimension.

RunningTotal

Returns the accumulative sum of values from the specified dimension for the specified member. The syntax for RunningTotal is:

RunningTotal(member, dimension, generation, grouping)

where:

  • member is the member in this dimension which you want to calculate the running totals for
  • dimension is the dimension on the opposite axis whose members will be used to calculate the running totals
  • generation is the generation of the members of the dimension to be calculated. A generation of 1 means to only include the root members in the calculation. A generation of 0 means that all members are to be included.
  • grouping is optional, and if it is present and set to GROUPDIM, when there are multiple dimensions on the opposite axis, separate running totals will be done for each grouping of the dimension that is not part of the running total definition. This will only have an effect when there is more than one dimension on the axis.

For example,

RunningTotal(All Products, All Locations, 2, GROUPDIM)

will add a calculated member ("Running Total" will be the name of the calculated member) to the All Products dimension, which contains the accumulative sum for each group of the generation 2 members on the All Locations dimension.

ifNotNumber

By default, missing or null values are treated as missing. You can substitute the function ifNotNumber for a member value to provide special case logic to handle missing or null values in the result set used in the calculation. The ifNotNumber function has the following syntax:

ifNotNumber(memberName,value)

where:

  • memberName is the name of the member in which the function operates on.
  • value is the numeric value which replaces the missing or null member value. The value specified must contain no commas.

 

Related Topics: