How to use Power BI DAX math functions with examples? Microsoft Power BI DAX provides various math or math functions like ROOF, FLOOR, CURRENCY, INT, SQRT, POWER, MOD, DIVIDE, ROUND, ROUNDUP, ROUNDDOWN, LCM, GCD, etc.
To demonstrate the mathematical functions of Power BI DAX, we are going to use the data shown below. As you can see, there are 15 records in this table.
Power BI DAX math functions
The following series of examples demonstrates the use of DAX math functions in Power BI
Ceiling feature of Power BI DAX
The DAX Ceiling function in Power BI returns the closest value, which is greater than or equal to a given value. The syntax for this Power BI CEILING function is:
CEILING (Number, significance)
To demonstrate these DAX Math functions in Power BI, we have to use the calculated column. To create a column, click the New column option in Power BI Home tab, or Modeling tongue.
As you can see in the screenshot below, we renamed the default column to CEIL. The following statement finds the closest integer value greater than or equal to Sales
CEILING (EmployeeSales [Sales])
Let me add this CEILING field to the table we created earlier. See the Create Table Report article to understand the steps required to create a table.
Power BI DAX FLOOR function
The Power BI DAX FLOOR function returns the closest value, which is less than or equal to a given value. The syntax for this Power BI DAX floor function is:
FLOOR (expression, significance)
This Power BI DAX math function finds the closest value that is less than or equal to Sales
FLOOR (EmployeeSales [Sales], 1)
ROUND function of Power BI DAX
The ROUND function of Power BI DAX is to round the given values to the nearest value. The syntax for this round Power BI DAX function is:
ROUND (expression, significance)
Round the Sales values to the nearest integer.
ROUND = ROUND (EmployeeSales [Sales], 0)
Power BI DAX CURRENCY function
The DAX CURRENCY function in Power BI converts the value to the currency data type. The syntax for this Power BI DAX CURRENCY function is:
CURRENCY (expression)
Converts the Sales values to the Currency data type
Money = CURRENCY (EmployeeSales [Sales])
Power BI DAX INT function
The DAX INT function in Power BI converts the given value to an integer data type. The syntax for this Power BI DAX INT function is:
INT (expression)
Power BI DAX INT function converts sales values to Integer data type
INT = INT (EmployeeSales [Sales])
Power BI DAX SQRT function
The DAX SQRT function in power BI returns the square root of a given number. The syntax for the Power BI DAX SQRT function is as follows:
SQRT (expression)
Returns the square root of the values in a sales column.
SQRT = SQRT (EmployeeSales [Sales])
Power BI DAX SIGN function
The Power BI DAX SIGN function returns the sign of a given number. This function returns 1 for positive values, -1 for negative values, and 0 for zeros. The syntax for this Power BI DAX SIGN function is:
SIGN (expression)
Returns the sign of the values in the grade of service column.
SIGN = SIGN (EmployeeSales [Service Grade])
Power BI DAX EVEN function
The DAX EVEN function in Power BI returns the closest even integer of a given number. The syntax for this Power BI DAX EVEN function is:
EVEN (expression)
The following statement returns the closest even integer of the values in a grade of service column
EVEN = EVEN (EmployeeSales [Service Grade])
Power BI DAX ODD function
The Power BI DAX ODD function returns the closest odd integer of a given number. The syntax for this Power BI DAX ODD function is:
ODD (expression)
Returns the closest odd integer of the values in a grade of service column.
EVEN = EVEN (EmployeeSales [Service Grade])
Power BI DAX FACT function
The Power BI DAX FACT function finds the factorial of a given number. The syntax for this Power BI DAX FACT function is:
FACT (number)
For example, factorial of 4 = 4 * 3 * 2 * 1 => 24
The following statement returns the factorial of an annual income divided by 1000
FACT = FACT (EmployeeSales [Yearly Income] / 1000)
Power BI DAX POWER function
The Power BI DAX POWER function finds the power of a given number. This function accepts a second argument to specify the power value. The syntax for this Power BI DAX POWER function is:
POWER (expression, raise_number)
For example, Power (4, 3) = 4³.
The following statement returns the factorial of an annual income divided by 1000
POWER = POWER (EmployeeSales [Sales], 2)
Power BI DAX DIVIDE function
The DAX DIVIDE function in Power BI divides one value against another. If there is an error or cannot be divided, it returns BLANK values. The syntax for this Power BI DAX DIVIDE function is:
DIVIDE (numerator, denominator)
The following statement divides the sales column values by 30
POWER = POWER (EmployeeSales [Sales], 2)
The Power BI DAX DIVIDE function also accepts the third argument. Use this argument to specify the alternate number for BLANK values.
The following statement divides the values in the sales column by the values in the rating column. If the result is blank, 9 is returned.
POWER = POWER (EmployeeSales [Sales], 2)
Power BI DAX ABS function
The ABS function in Power BI DAX returns the absolute positive value. The syntax for this Power BI DAX ABS function is:
ABS (expression)
Returns the absolute positive value of the values in the grade of service column.
ABS = ABS (EmployeeSales [Service Grade])
Power BI DAX MOD function
The DAX MOD function in Power BI returns the remainder after the divided number. The syntax for this Power BI DAX MOD function is:
MOD (number, divisor)
For example MOD (5, 2) = 1.
The following statement returns the remainder of Sales divisible by the result of the SQRT function.
MOD = MOD (EmployeeSales [Sales], EmployeeSales [SQRT])
Power BI DAX MROUND function
The DAX MROUND function returns a number rounded to the specified multiple. The syntax for this Power BI DAX MRound function is:
MROUND (expression, multiple)
The declaration we use is:
MROUND = MROUND (EmployeeSales [Sales], 0.05)
Power BI DAX Quotient Function
The DAX QUOTIENT function in Power BI returns the integer part of the division result. The syntax for the Power BI DAX QUOTIENT function is:
QUOTIENT (numerator, denominator)
The following statement returns the integer part of Sales divided by the result of the Mod function:
QUOTIENT = QUOTIENT (EmployeeSales [Sales], EmployeeSales [MOD])
Power BI DAX RAND function
The Power BI DAX RAND function returns the random positive number between 0 and 1. The syntax for the Power BI DAX RAND function is:
RAND ()
Let me create a new column to generate random values
RAND = RAND ()
Power BI DAX RANDBETWEEN function
The DAX RANDBETWEEN function in Power BI returns the random number between the starting and ending values specified by the user. The syntax for the Power BI DAX RANDBETWEEN function is:
RANDBETWEEN (start, end)
Let me create a new column to generate random values between 10 and 50
RANDBET = RANDBETWEEN (10, 50)
Power BI DAX TRUNC function
The Power BI DAX TRUNC function truncates the given number to an integer by removing the decimals. The syntax for the Power BI DAX TRUNC function is:
TRUNC (number, num_of_digits)
This Power BI DAX math function truncates the sales column values
TRUNC = TRUNC (EmployeeSales [Sales], 1)
ROUNDDOWN function of Power BI DAX
The ROUNDDOWN function in Power BI DAX rounds the given number to zero. The syntax for the Power BI DAX ROUNDDOWN function is:
ROUNDDOWN (number, num_of_digits)
Rounds down the values in the Sales column to 2 digits.
ROUNDDOWN = ROUNDDOWN (EmployeeSales [Sales], -2)
ROUNDUP function of Power BI DAX
The ROUNDUP function in Power BI DAX rounds the given number from zero. The syntax for the Power BI DAX ROUNDUP function is:
ROUNDUP (number, num_of_digits)
The following statement rounds the values in the Sales column to 2 digits (that is, greater than the original).
ROUNDUP = ROUNDUP (EmployeeSales [Sales], -2)
Power BI DAX LCM function
The DAX LCM function finds the least common or least common of several integer values. The syntax for this Power BI DAX LCM function is:
LCM (expression1, expression2, .....)
Returns the least common of the SQRT result and the result of the MOD function
LCM = LCM (EmployeeSales [SQRT], EmployeeSales [MOD])
Power BI DAX GCD function
The Power BI DAX GCD function finds the greatest common divisor of multiple integer values. The syntax for the Power BI DAX GCD function is:
GCD (expression1, expression2, .....)
This Power BI DAX math function returns the result of the largest common divisor function of SQRT and the result of MOD
GCD = GCD (EmployeeSales [SQRT], EmployeeSales [MOD])
