If a function can have more than one argument, and you want to add an argument, click the Add argument button.
Missing values are ignored. For example, if the expression is Average(East, West, South)
and South
is missing, the calculation finds the average of East
and West
.
The following table shows the functions that you can add to your expression.
Function |
Description |
Abs
|
Returns the absolute value of a number. Abs accepts only one argument. |
Average
|
Returns the average of all the values in the arguments. The average is the sum of the values divided by the count. If the count is zero, Average returns a missing value. Average accepts any number of arguments. For example:
Average(Product 1, Product 2) returns the average of Product 1 and Product 2 .
Average(Gen(2)) returns the average of all members with a generation level of 2.
|
Count
|
Returns the count of all the values in the arguments. If there are no values to count, zero is returned. Count accepts any number of arguments.
Count(Product 1, Product 2, Gen(2)) returns 2 + the number of members with a generation level of 2
|
If
|
Returns a value based on whether a given condition is true. If accepts three arguments, where:
- The first argument is the condition to test. The condition has a left part and a right part, separated by one of the following operators: <=, >=, =, <, >, or != (<> can also be used for not equal tests).
- The second argument is the value to return if the condition is true.
- The third argument is the value to return if the condition is false.
|
Max
|
Returns the maximum value from all the values in the arguments. Max accepts any number of arguments.
|
Median
|
Returns the median value from all the values in the arguments. The median value is the value at which half the numbers are above the value and half the numbers are below the value. Median accepts any number of arguments.
|
Min
|
Returns the minimum value from all the values in the arguments. Min accepts any number of arguments.
|
Power
|
Returns the value of a first value raised to the power of a second value. Power accepts two arguments, where:
- The first argument is the base.
- The second argument is the exponent.
Power returns the base raised to the power of the exponent.
|
Product
|
Returns the multiplication product of all the values in the arguments. Product accepts any number of arguments.
|
Round
|
Returns the integer part of the number rounded to the
nearest whole number. Round accepts only one argument. |
Sqrt
|
Returns the square root of a number. Sqrt accepts only one argument. |
Stdev
|
Returns the standard deviation of all the values in the
arguments. The standard deviation is a measure of how widely values are
dispersed from the average value. Stdev accepts any number of arguments.
|
Sum
|
Returns the sum of all the values in the arguments.
If there are no values to add, Sum returns zero. Sum accepts any number of arguments.
Sum(Product 1, Product 2) returns the sum of Product 1 and Product 2 .
Sum(Gen(2)) returns the sum of all members with a generation level of 2.
|
Var
|
Returns the variance of all the values in the arguments. The variance is the average squared deviation
of each number in the set from the average. Var accepts any number of arguments. |
Percentoftotal
|
Note: Percentoftotal applies only to calculations on multidimensional databases.
Returns a specified member as a percentage of some total. The total is the value of the cell at the intersection of the specified member and another member on the other axis. For example, a grid shows sales, where Time is on the rows and Products is on the columns. You can find the percentage of total for Product 1, where the total is the sales for Product 1 for All Time . The calculated member returns the sales for Product 1 for each time period divided by the sales for Product 1 for All Time .
Percentoftotal has the following arguments:
- Member
- Is the member in the dimension (that is, column or row) that you want to calculate the percentage of total for.
- Dimension
- Is the dimension on the opposite axis that contains the member that is used to determine the total. For example, if the grid shows
Time and Locations on the rows, and Products on the columns, and Member is a member of Products , then Dimension can be either Time or Locations .
- Opposite axis member
- Is the member of Dimension that, with Member, determines the total to calculate the percentage with. The total is the value of the cell at the intersection of Member and Opposite axis member.
- Generation
- Is the generation that the percentages are calculated for. A generation of 0 means that all members show the percentages.
- Grouping
- When the axis has more than one dimension, specifies if each grouping of the dimensions has a separate percentage. If Grouping is set to ungrouped, the percentages are calculated across groups.
For example, the grid below shows the calculated member Percentages whose expression is a Percentoftotal function. The Percentoftotal function has the following values for its arguments:
- Member = Product
- Dimension = Locations
- Opposite axis member = All Locations
- Generation = 1
- Grouping = Grouped
The calculated member percentages is calculated as the sales for the product at each location as a percentage of the sales for the product at all locations.
Locations | Time | Product | Percentages |
East |
2000 | 5 | 0.25 |
2001 | 20 | 0.40 |
2002 | 20 | 0.50 |
West |
2000 | 15 | 0.75 |
2001 | 30 | 0.60 |
2002 | 20 | 0.50 |
All Locations |
2000 | 20 | 1 |
2001 | 50 | 1 |
2002 | 40 | 1 |
|
Rank
|
Returns the ranking of each item in a column or row.
Rank has the following arguments:
- Member
- Is the member in the dimension (that is, column or row) that you want to rank.
- Dimension
- Is the dimension on the opposite axis whose members are used to generate the rank. For example, the grid shows sales, where
Time and Locations are on the rows, and Products is on the columns, and Member is a member of Products . Dimension can be either Time or Locations . If Dimension is Time , then Rank ranks sales of Products over Time .
- Generation
- Is the generation of the members of the dimension to rank. A generation of 0 means that all members are ranked.
- Order
- Is either ascending or descending. In descending order, the largest number will be ranked 1. In ascending order, the smallest number will be ranked 1.
- Grouping
- When the axis has more than one dimension, specifies if each grouping of the dimensions is ranked separately. If Grouping is set to ungrouped, the ranks are calculated across groups.
Note: The Dimension, Generation, and Grouping arguments apply only to calculations on multidimensional databases.
For example, the grid below shows the calculated member Rank whose expression is a Rank function. The Rank function has the following values for its arguments:
- Member = Product
- Dimension = Time
- Generation = 1
- Order = Descending
- Grouping = Grouped
Locations | Time | Product | Rank |
East |
2000 | 102 | 1 |
2001 | 76 | 3 |
2002 | 88 | 2 |
West |
2000 | 49 | 3 |
2001 | 52 | 1 |
2002 | 51 | 2 |
|
Runningtotal
|
Returns the accumulative sum of values from the specified
dimension for the specified member.
Runningtotal has the following arguments:
- Member
- Is the member in the dimension (that is, row or column) that you want to calculate the running totals for.
- Dimension
- Is the dimension on the opposite axis whose members are used to calculate the running totals. For example, the grid shows sales, where
Time and Locations on the rows, and Products on the columns, and Member is a member of Products . Dimension can be either Time or Locations . If Dimension is Time , then Runningtotal returns the running total of sales for Products over Time .
- Generation
- Is the generation of the members of the dimension to total. A generation of 0 means that all members are totalled.
- Grouping
- When the axis has more than one dimension, specifies if each grouping of the dimensions is totalled separately. If Grouping is set to ungrouped, the totals are calculated across groups.
Note: The Dimension, Generation, and Grouping arguments apply only to calculations on multidimensional databases.
For example, the grid below shows the calculated member RunningTotal whose expression is a Runningtotal function. The Runningtotal function has the following values for its arguments:
- Member = Product
- Dimension = Time
- Generation = 1
- Grouping = Grouped
Locations | Time | Product | RunningTotal |
East |
2000 | 102 | 102 |
2001 | 76 | 178 |
2002 | 88 | 266 |
West |
2000 | 49 | 49 |
2001 | 52 | 101 |
2002 | 51 | 152 |
|