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:
Original article (before wiki edits) was written by Jeannine Takaki and JuanPablo Jofre and formatted by Mary Browning, Microsoft SQL Server Technical Writers.
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.
ABS(<number>)
Number. The number for which you want the absolute value.
Returns a number (R8).
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])
↑ Back to top
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 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:
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.
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)
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.
EXP(<number>)
Number. The exponent applied to the base e. The constant e equals 2.71828182845904, the base of the natural logarithm.
The following formula calculates e raised to the power of the number contained in the column, [Power].
[Power]
=EXP([Power])
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.
FACT(<number>)
Number. The non-negative number for which you want to calculate the factorial.
Returns a number (I8).
The following formula returns the factorial for the series of integers in the column, [Values].
[Values]
=FACT([Values])
The following table shows the expected results.
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.
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.
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.
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.
TRUNC(-4.3)
INT(-4.3)
INT(<number>)
Number. The number you want to round down to an integer.
The following expression rounds the value to 1. If you use the ROUND function, the result would be 2.
=INT(1.5)
The result type is usually the same type of the significance used as argument with the following exceptions:
ISO.CEILING(<number>[, <significance>])
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.
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)
The following formula returns the ISO ceiling value of -4.40.
=ISO.CEILING(-4.42,0.05)
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.
LN(<number>)
Number. The positive number for which you want the natural logarithm.
The following example returns the natural logarithm of the number in the column, [Values].
=LN([Values])
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.
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.
The following formulas return the same result, 2.
=LOG(100,10) =LOG(100) =LOG10(100)
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.
LOG10(<number>)
Number. A positive number for which you want the base-10 logarithm.
The following formulas return the same result, 2:
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.
MROUND(<number>, <multiple>)
Number. The number to round.
Multiple. The multiple of significance to which you want to round the number.
The following expression rounds 1.3 to the nearest multiple of .2. The expected result is 1.4.
=MROUND(-1.3,0.2)
The following expression rounds -10 to the nearest multiple of -3. The expected result is -9.
=MROUND(-10,-3)
The following expression returns an error, because the numbers have different signs.
=MROUND(5,-2)
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.
PI()
The following formula calculates the area of a circle given the radius in the column, [Radius].
[Radius]
=PI()*([Radius]*2)
Returns the result of a number raised to a power.
POWER(<number>, <power>)
Number. The base number, which can be any real number.
Power. The exponent to which the base number is raised.
The following example returns 25.
=POWER(5,2)
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.
QUOTIENT(<numerator>, <denominator>)
Numerator. The dividend, or number to divide.
Denominator. The divisor, or number to divide by.
=QUOTIENT(5,2) =QUOTIENT(10/2,2)
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.
RAND()
To generate a random real number between two other numbers, you can use a formula like the following:
= RAND()*(int1-int2)+int1
Returns a random number in the range between two numbers you specify.
RANDBETWEEN(<bottom>,<top>)
Bottom. The smallest integer the function will return.
Top. The largest integer the function will return.
The following formula returns a random number between 1 and 10.
=RANDBETWEEN(1,10)
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.
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.
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)
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.
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.
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.
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)
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.
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.
The following formula rounds Pi to four decimal places. The expected result is 3.1416.
=ROUNDUP(PI(),4)
The following formula rounds 1.3 to the nearest multiple of 0.2. The expected result is 1.4.
=ROUNDUP(1.3,0.2)
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.
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.
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.
The following formula returns the sign of the result of the expression that calculates sale price minus cost.
=SIGN( ([Sale Price] - [Cost]) )
Returns the square root of a number.
If the number is negative, the SQRT function returns an error.
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.
The following formula returns 5.
=SQRT(25)
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.
SUM(<column>)
Column. The column that contains the numbers to sum.
The following example adds all the numbers that are contained in the column, Amt, from the table, Sales.
=SUM(Sales[Amt])
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.
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).
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.
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.
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).
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)
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.