The Calculation Editor offers the following types of functions:
The following table shows the arithmetic functions supported.
Arithmetic Functions |
Description |
|
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:
|
|
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. |
|
Returns the count of all numbers in the definition. Missing
values are ignored. If there are no values to count, zero is returned.
|
|
Returns the highest value in all the numbers in the definition.
|
|
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. |
|
Returns the lowest value in all the numbers in the definition.
|
|
Returns the multiplication of all the values in the definition.
|
|
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:
|
|
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:
|
|
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). |
|
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. |
|
Returns the variance, which the average squared deviation of each number in the set from the average. |
The following table shows special functions supported:
Special Functions |
Description |
|
Returns all children of the specified member. For example:
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:
rather than
|
|
Returns all leaf-level descendants of the specified member. For example:
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:
rather than
|
|
Returns the values from the specified dimensions in ascending
or descending order for the specified member. The syntax for
where:
For example:
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. |
|
Returns the accumulative sum of values from the specified
dimension for the specified member. The syntax for
where:
For example,
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. |
|
By default, missing or null values are treated as missing.
You can substitute the function
where:
|