Power BI: DAX: Math and Trigonometric Functions
The mathematical functions in Data Analysis Expressions (DAX) are very similar to the Excel mathematical and trigonometric functions.
Read about the following math and trigonometric functions:
- ABS
- CEILING
- EXP
- FACT
- FLOOR
- INT
- ISO.CEILING
- LN
- LOG
- LOG10
- MROUND
- PI
- POWER
- QUOTIENT
- RAND
- RANDBETWEEN
- ROUND
- ROUNDDOWN
- ROUNDUP
- SIGN
- SQRT
- SUM
- SUMX
- TRUNC
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].
=PI()*([Radius]*2)
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)