Resources For IT Professionals

# PowerPivot: DAX: Math and Trigonometric Functions

The mathematical functions in Data Analysis Expressions (DAX) are very similar to the Excel mathematical and trigonometric functions.

Original article (before wiki edits) was written by Jeannine Takaki and JuanPablo Jofre and formatted by Mary Browning, Microsoft SQL Server Technical Writers.

### ABS

Returns the absolute value of a number.

The absolute value of a number is a real number, whole or decimal, without its sign. You can use the ABS function to ensure that only non-negative numbers are returned from expressions when nested in functions that require a positive number.

#### Syntax

ABS(<number>)

Number. The number for which you want the absolute value.

Returns a number (R8).

#### Example

The following example returns the absolute value of the difference between the list price and the dealer price, which you might use in a new calculated column, DealerMarkup.

=ABS([DealerPrice]-[ListPrice])

### CEILING

Rounds a number up, to the nearest integer or to the nearest multiple of significance.

There are two CEILING functions in DAX, with the following differences:

• The CEILING function emulates the behavior of the CEILING function in Excel.
• The ISO.CEILING function follows the ISO-defined behavior for determining the ceiling value.

The two functions return the same value for positive numbers, but different values for negative numbers. When using a positive multiple of significance, both CEILING and ISO.CEILING round negative numbers upward (toward positive infinity). When using a negative multiple of significance, CEILING rounds negative numbers downward (toward negative infinity), while ISO.CEILING rounds negative numbers upward (toward positive infinity).

The return type is usually of the same type of the significant argument, with the following exceptions:

• If the number argument type is currency, the return type is currency.
• If the significance argument type is Boolean, the return type is integer.
• If the significance argument type is non-numeric, the return type is real.

#### Syntax

CEILING(<number>, <significance>)

Number. The number you want to round, or a reference to a column that contains numbers.

Significance. The multiple of significance to which you want to round. For example, to round to the nearest integer, type 1.

Returns a number rounded as specified.

#### Example

The following formula returns 4.45. This might be useful if you want to avoid using smaller units in your pricing. If an existing product is priced at \$4.42, you can use CEILING to round prices up to the nearest unit of five cents.

=CEILING(4.42,0.05)

The following formula returns similar results as the previous example, but uses numeric values stored in the column, ProductPrice.

=CEILING([ProductPrice],0.05)

### EXP

Returns e raised to the power of a given number. The constant e equals 2.71828182845904, the base of the natural logarithm.

EXP is the inverse of LN, which is the natural logarithm of the given number.

To calculate powers of bases other than e, use the exponentiation operator (^). For more information, see the DAX Operator Reference.

#### Syntax

EXP(<number>)

Number. The exponent applied to the base e. The constant e equals 2.71828182845904, the base of the natural logarithm.

Returns a number (R8).

#### Example

The following formula calculates e raised to the power of the number contained in the column, `[Power]`.

=EXP([Power])

### FACT

Returns the factorial of a number, equal to the series 1*2*3*...* , ending in the given number.

If the number is not an integer, it is truncated and an error is returned. If the result is too large, an error is returned.

#### Syntax

FACT(<number>)

Number. The non-negative number for which you want to calculate the factorial.

Returns a number (I8).

#### Example

The following formula returns the factorial for the series of integers in the column, `[Values]`.

=FACT([Values])

The following table shows the expected results.

 Values Results 0 1 1 1 2 2 3 6 4 24 5 120

### FLOOR

Rounds a number down, toward zero, to the nearest multiple of significance.

If either argument is nonnumeric, FLOOR returns an error.

If number and significance have different signs, FLOOR returns an error.

Regardless of the sign of the number, a value is rounded down when adjusted away from zero. If the number is an exact multiple of significance, no rounding occurs.

#### Syntax

FLOOR(<number>, <significance>)

Number. The numeric value you want to round.

Significance. The multiple to which you want to round. The arguments number and significance must either both be positive, or both be negative.

Returns a number (R8).

#### Example

The following formula takes the values in the [Total Product Cost] column from the table, InternetSales, and rounds down to the nearest tenth (one decimal place).

=FLOOR(InternetSales[Total Product Cost],.5)

The following table shows the expected results for some sample values.

 Values Expected Results 10.8423 10.8 8.0373 8 2.9733 2.9

### INT

Rounds a number down to the nearest integer.

TRUNC and INT are similar in that both return integers. TRUNC removes the fractional part of the number. INT rounds numbers down to the nearest integer based on the value of the fractional part of the number. INT and TRUNC are different only when using negative numbers: `TRUNC(-4.3)` returns -4, but `INT(-4.3)` returns -5 because -5 is the lower number.

#### Syntax

INT(<number>)

Number. The number you want to round down to an integer.

Returns a number (I8).

#### Example

The following expression rounds the value to 1. If you use the ROUND function, the result would be 2.

=INT(1.5)

### ISO.CEILING

Rounds a number up, to the nearest integer or to the nearest multiple of significance.

There are two CEILING functions in DAX, with the following differences:

• The CEILING function emulates the behavior of the CEILING function in Excel.
• The ISO.CEILING function follows the ISO-defined behavior for determining the ceiling value.

The two functions return the same value for positive numbers, but different values for negative numbers. When using a positive multiple of significance, both CEILING and ISO.CEILING round negative numbers upward (toward positive infinity). When using a negative multiple of significance, CEILING rounds negative numbers downward (toward negative infinity), while ISO.CEILING rounds negative numbers upward (toward positive infinity).

The result type is usually the same type of the significance used as argument with the following exceptions:

• If the first argument is of currency type then the result will be currency type.
• If the optional argument is not included the result is of integer type.
• If the significance argument is of Boolean type then the result is of integer type.
• If the significance argument is non-numeric type then the result is of real type.

#### Syntax

ISO.CEILING(<number>[, <significance>])

Number. The number you want to round, or a reference to a column that contains numbers.

Significance. (optional) The multiple of significance to which you want to round. For example, to round to the nearest integer, type 1. If the unit of significance is not specified, the number is rounded up to the nearest integer.

Returns a number rounded as specified.

#### Example: Positive Numbers

The following formula returns 4.45. This might be useful if you want to avoid using smaller units in your pricing. If an existing product is priced at \$4.42, you can use ISO.CEILING to round prices up to the nearest unit of five cents.

=ISO.CEILING(4.42,0.05)

#### Example: Negative Numbers

The following formula returns the ISO ceiling value of -4.40.

=ISO.CEILING(-4.42,0.05)

### LN

Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).

LN is the inverse of the EXP function.

#### Syntax

LN(<number>)

Number. The positive number for which you want the natural logarithm.

Returns a number (I8).

#### Example

The following example returns the natural logarithm of the number in the column, `[Values]`.

=LN([Values])

### LOG

Returns the logarithm of a number to the base you specify.

You might receive an error if the value is too large to be displayed.

The function LOG10 is similar, but always returns the common logarithm, meaning the logarithm for the base 10.

#### Syntax

LOG(<number>,<base>)

Number. The positive number for which you want the logarithm.

Base. The base of the logarithm. If omitted, the base is 10.

Returns a number (R8).

#### Example

The following formulas return the same result, 2.

=LOG(100,10)
=LOG(100)
=LOG10(100)

### LOG10

Returns the base-10 logarithm of a number.

The LOG function lets you change the base of the logarithm, instead of using the base 10.

#### Syntax

LOG10(<number>)

Number. A positive number for which you want the base-10 logarithm.

Returns a number (R8).

#### Example

The following formulas return the same result, 2:

=LOG(100,10)
=LOG(100)
=LOG10(100)

### MROUND

Returns a number rounded to the desired multiple.

MROUND rounds up, away from zero, if the remainder of dividing number by the specified multiple is greater than or equal to half the value of multiple.

#### Syntax

MROUND(<number>, <multiple>)

Number. The number to round.

Multiple. The multiple of significance to which you want to round the number.

Returns a number (R8).

#### Example: Decimal Places

The following expression rounds 1.3 to the nearest multiple of .2. The expected result is 1.4.

=MROUND(-1.3,0.2)

#### Example: Negative Numbers

The following expression rounds -10 to the nearest multiple of -3. The expected result is -9.

=MROUND(-10,-3)

#### Example: Error

The following expression returns an error, because the numbers have different signs.

=MROUND(5,-2)

### PI

Returns the value of Pi, 3.14159265358979, accurate to 15 digits.

Pi is a mathematical constant. In PowerPivot, Pi is represented as a real number accurate to 15 digits, the same as in Excel.

#### Syntax

PI()

Returns a number (R8).

#### Example

The following formula calculates the area of a circle given the radius in the column, `[Radius]`.

### POWER

Returns the result of a number raised to a power.

#### Syntax

POWER(<number>, <power>)

Number. The base number, which can be any real number.

Power. The exponent to which the base number is raised.

Returns a number (R8).

#### Example

The following example returns 25.

=POWER(5,2)

### QUOTIENT

Performs division and returns only the integer portion of the division result. Use this function when you want to discard the remainder of division.

If either argument is non-numeric, QUOTIENT returns an error.

You can use a column reference instead of a literal value for either argument. However, if the column that you reference contains a 0 (zero), an error is returned for the entire column of values.

#### Syntax

QUOTIENT(<numerator>, <denominator>)

Numerator. The dividend, or number to divide.

Denominator. The divisor, or number to divide by.

Returns a number (I8).

#### Example

The following formulas return the same result, 2.

=QUOTIENT(5,2)
=QUOTIENT(10/2,2)

### RAND

Returns a random number greater than or equal to 0 and less than 1, evenly distributed. The number that is returned changes each time the cell containing this function is recalculated.

In PowerPivot workbooks, recalculation depends on various factors, including whether the workbook is set to Manual or Automatic recalculation mode, and whether data has been refreshed. This is different from Microsoft Excel, where you can control when RAND generates a new random number by turning off recalculation.

For more information, see Refreshing or Changing Imported Data and Recalculating Formulas in the TechNet Library.

RAND and other volatile functions that do not have fixed values are not always recalculated. For example, execution of a query or filtering will usually not cause such functions to be re-evaluated. However, the results for these functions will be recalculated when the entire column is recalculated. These situations include refresh from an external data source or manual editing of data that causes re-evaluation of formulas that contain these functions.

Moreover, RAND is always recalculated if the function is used in the definition of a measure.

Also, in such contexts the RAND function cannot return a result of zero, to prevent errors such as division by zero.

#### Syntax

RAND()

Returns a number (R8).

#### Example

To generate a random real number between two other numbers, you can use a formula like the following:

= RAND()*(int1-int2)+int1

### RANDBETWEEN

Returns a random number in the range between two numbers you specify.

#### Syntax

RANDBETWEEN(<bottom>,<top>)

Bottom. The smallest integer the function will return.

Top. The largest integer the function will return.

Returns a number (I8).

#### Example

The following formula returns a random number between 1 and 10.

=RANDBETWEEN(1,10)

### ROUND

Rounds a number to the specified number of digits.

If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.

If num_digits is 0, the number is rounded to the nearest integer.

If num_digits is less than 0, the number is rounded to the left of the decimal point.

Related Functions

To always round up (away from zero), use the ROUNDUP function.

To always round down (toward zero), use the ROUNDDOWN function.

To round a number to a specific multiple (for example, to round to the nearest multiple of 0.5), use the MROUND function.

You can use the functions TRUNC and INT to obtain the integer portion of the number.

#### Syntax

ROUND(<number>, <num_digits>)

Number. The number you want to round.

Num_digits. The number of digits to which you want to round. A negative value rounds digits to the left of the decimal point; a value of zero rounds to the nearest integer.

Returns a number (R8).

#### Example

The following formula rounds 2.15 up, to one decimal place. The expected result is 2.2.

=ROUND(2.15,1)

The following formula rounds 21.5 to one decimal place to the left of the decimal point. The expected result is 20.

=ROUND(21.5,-1)

### ROUNDDOWN

Rounds a number down, toward zero.

If num_digits is greater than 0 (zero), then the value in number is rounded down to the specified number of decimal places.

If num_digits is 0, then the value in number is rounded down to the nearest integer.

If num_digits is less than 0, then the value in number is rounded down to the left of the decimal point.

Related Functions

ROUNDDOWN behaves like ROUND, except that it always rounds a number down. The INT function also rounds down, but with INT the result is always an integer, whereas with ROUNDDOWN you can control the precision of the result.

#### Syntax

ROUNDDOWN(<number>, <num_digits>)

Number. A real number that you want to round down.

Num_digits. The number of digits to which you want to round. Negative rounds to the left of the decimal point; zero to the nearest integer.

Returns a number (R8).

#### Example

The following example rounds 3.14159 down to three decimal places. The expected result is 3.141.

=ROUNDDOWN(3.14159,3)

The following example rounds the value of 31415.92654 down to 2 decimal places to the left of the decimal. The expected result is 31400.

=ROUNDDOWN(31415.92654, -2)

### ROUNDUP

Rounds a number up, away from 0 (zero).

ROUNDUP behaves like ROUND, except that it always rounds a number up.

• If num_digits is greater than 0 (zero), then the number is rounded up to the specified number of decimal places.

• If num_digits is 0, then number is rounded up to the nearest integer.

• If num_digits is less than 0, then number is rounded up to the left of the decimal point.

Related Functions

ROUNDUP behaves like ROUND, except that it always rounds a number up.

#### Syntax

ROUNDUP(<number>, <num_digits>)

Number. A real number that you want to round up.

Num_digits. The number of digits to which you want to round. A negative value for num_digits rounds to the left of the decimal point; if num_digits is zero or is omitted, number is rounded to the nearest integer.

Returns a number (R8).

#### Example

The following formula rounds Pi to four decimal places. The expected result is 3.1416.

=ROUNDUP(PI(),4)

#### Example: Decimals as Second Argument

The following formula rounds 1.3 to the nearest multiple of 0.2. The expected result is 1.4.

=ROUNDUP(1.3,0.2)

#### Example: Negative Number as Second Argument

The following formula rounds the value in the column, FreightCost, with the expected results shown in the following table.

=ROUNDUP([Values],-1)

When num_digits is less than zero, the number of places to the left of the decimal sign is increased by the value you specify.

 FreightCost Expected Result 13.25 20 2.45 10 25.56 30 1.34 10 345.01 350

### SIGN

Determines the sign of a number, the result of a calculation, or a value in a column. The function returns 1 if the number is positive, 0 (zero) if the number is zero, or -1 if the number is negative.

#### Syntax

SIGN(<number>)

Number. Any real number, a column that contains numbers, or an expression that evaluates to a number.

Returns a number (I8). The possible return values are 1, 0, and -1.

 Return Value Description 1 The number is positive. 0 The number is zero. -1 The number is negative.

#### Example

The following formula returns the sign of the result of the expression that calculates sale price minus cost.

=SIGN( ([Sale Price] - [Cost]) )

### SQRT

Returns the square root of a number.

If the number is negative, the SQRT function returns an error.

#### Syntax

SQRT(<number>)

Number. The number for which you want the square root, a column that contains numbers, or an expression that evaluates to a number.

Returns a number (R8).

#### Example

The following formula returns 5.

=SQRT(25)

### SUM

Adds all the numbers in a column.

If any rows contain non-numeric values, blanks are returned.

If you want to filter the values that you are summing, you can use the SUMX function and specify an expression to sum over.

#### Syntax

SUM(<column>)

Column. The column that contains the numbers to sum.

Returns a number (R8).

#### Example

The following example adds all the numbers that are contained in the column, Amt, from the table, Sales.

=SUM(Sales[Amt])

### SUMX

Returns the sum of an expression evaluated for each row in a table.

The SUMX function takes as its first argument a table, or an expression that returns a table. The second argument is a column that contains the numbers you want to sum, or an expression that evaluates to a column.

Only the numbers in the column are counted. Blanks, logical values, and text are ignored.

To see some more complex examples of SUMX in formulas, see ALL and CALCULATETABLE.

#### Syntax

SUMX(<table>, <expression>)

Table. The table containing the rows for which the expression will be evaluated.

Expression. The expression to be evaluated for each row of the table.

Returns a number (R8 or I8: depends on the data type of the expression being added).

#### Example

The following example first filters the table, InternetSales, on the expression, ShippingTerritoryID = 5, and then returns the sum of all values in the column, Freight. In other words, the expression returns the sum of freight charges for only the specified sales area.

=SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight])

If you do not need to filter the column, use the SUM function. The SUM function is similar to the Excel function of the same name, except that it takes a column as a reference.

### TRUNC

Truncates a number to an integer by removing the decimal, or fractional, part of the number.

TRUNC and INT are similar in that both return integers. TRUNC removes the fractional part of the number. INT rounds numbers down to the nearest integer based on the value of the fractional part of the number. INT and TRUNC are different only when using negative numbers: TRUNC(-4.3) returns -4, but INT(-4.3) returns -5 because -5 is the smaller number.

#### Syntax

TRUNC(<number>,<num_digits>)

Number. The number you want to truncate.

Num_digits. A number specifying the precision of the truncation; if omitted, 0 (zero).

Returns a number (I8).

#### Example

The following formula returns 3, the integer part of pi.

=TRUNC(PI())

The following formula returns -8, the integer part of -8.9.

=TRUNC(-8.9)

Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
• Ed Price MSFT edited Revision 16. Comment: Updated SUMX syntax, per review with Howie.

• Ed Price MSFT edited Revision 12. Comment: Removed some spaces.

• Ed Price MSFT edited Revision 11. Comment: Updated External Link.

• Ed Price MSFT edited Original. Comment: Added DAX link at top.

Page 1 of 1 (4 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.