Microsoft Power BI DAX provides various mathematical Functions such as: CEILING, FLOOR, CURRENCY, INT, SQRT, POWER, MOD, DIVIDE, ROUND, ROUNDUP, ROUNDDOWN, LCM, GCD etc. In this article we will how you, How to use Power BI DAX Math Functions with examples.
To demonstrate the Power BI DAX Math functions, we are going to use the below shown data. As you can see, there are 15 records in this table.
Power BI DAX Math Functions
The following series of examples will show you the use of DAX Math Functions in Power BI
Power BI DAX CEILING Function
The DAX Ceiling function return closest value which is greater than or equal to a given value. The syntax of this POWER BI CEILING Function is:
CEILING(Number, significance)
To demonstrate these DAX Math functions, we have to use calculated column. In order to create a column, please click on the New Column option under the Home tab, or Modeling tab.
As you can see from the below screenshot, we renamed the default column name as CEIL. Below statement will find the closest integer value greater than or equal to Sales
CEILING(EmployeeSales[Sales])
Let me add this CEILING field to the table that we created earlier. Please refer Create Table Report article to understand the steps involved in creating a table
Power BI DAX FLOOR Function
The DAX FLOOR function is used to return the closest value which is less than or equal to a given value. The syntax of this Power BI DAX Floor Function is:
FLOOR(expression, significance)
It will find the closest value which is less than or equal to Sales
FLOOR(EmployeeSales[Sales], 1)
Power BI DAX ROUND Function
The DAX ROUND function is used to round the given values to closest value. The syntax of this Power BI DAX Round Function is:
ROUND(expression, significance)
It will round the Sales values to nearest integer
ROUND = ROUND(EmployeeSales[Sales], 0)
Power BI DAX CURRENCY Function
The DAX CURRENCY function convert the value into currency data type. The syntax of this Power BI DAX CURRENCY Function is:
CURRENCY(expression)
It will convert the Sales values to Currency data type
Money = CURRENCY(EmployeeSales[Sales])
Power BI DAX INT Function
The DAX INT function convert the given value into integer data type. The syntax of this Power BI DAX INT Function is:
INT(expression)
It will convert the Sales values to Integer data type
INT = INT(EmployeeSales[Sales])
Power BI DAX SQRT Function
The DAX SQRT function returns the square root of a given number. Syntax of Power BI DAX SQRT Function is as shown below:
SQRT(expression)
It returns the square root of a Sales column values
SQRT = SQRT(EmployeeSales[Sales])
Power BI DAX SIGN Function
The 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 of this Power BI DAX SIGN Function is:
SIGN(expression)
It returns the sign of the Service Grade column values
SIGN = SIGN(EmployeeSales[Service Grade])
Power BI DAX EVEN Function
The DAX EVEN function returns the nearest even integer of a given number. The syntax of this Power BI DAX EVEN Function is:
EVEN(expression)
Below statement returns the nearest even integer of a Service Grade column values
EVEN = EVEN(EmployeeSales[Service Grade])
Power BI DAX ODD Function
The DAX ODD function returns the nearest odd integer of a given number. The syntax of this Power BI DAX ODD Function is:
ODD(expression)
It returns the nearest odd integer of a Service Grade column values
EVEN = EVEN(EmployeeSales[Service Grade])
Power BI DAX FACT Function
The DAX FACT function will find the factorial of a given number. The syntax of this Power BI DAX FACT Function is:
FACT(number)
For example factorial of 4 = 4 * 3 * 2 * 1 => 24
Below statement returns the factorial of a yearly income divide by 1000
FACT = FACT(EmployeeSales[Yearly Income] / 1000)
Power BI DAX POWER Function
The DAX POWER function will find the Power of a given number. This function accepts second argument to specify the power value. The syntax of this Power BI DAX POWER Function is:
POWER(expression, raise_number)
For example Power(4, 3) = 4³.
Below statement returns the factorial of a yearly income divide by 1000
POWER = POWER(EmployeeSales[Sales], 2)
Power BI DAX DIVIDE Function
The DAX DIVIDE function divides one value against another. If there is an error or unable to divide then it returns BLANK values. The syntax of this Power BI DAX DIVIDE Function is:
DIVIDE(numerator, denominator)
Below statement divides sales column values by 30
POWER = POWER(EmployeeSales[Sales], 2)
Power BI DAX DIVIDE Function also accepts third argument. Use this argument to specify the alternative number for BLANK values.
Below statement divides sales column values by Grade column values. If the result is blank then 9 will be returned.
POWER = POWER(EmployeeSales[Sales], 2)
Power BI DAX ABS Function
The DAX ABS function returns the absolute positive value. The syntax of this Power BI DAX ABS Function is:
ABS(expression)
It returns the absolute positive value from Service Grade column values
ABS = ABS(EmployeeSales[Service Grade])
Power BI DAX MOD Function
The DAX MOD function returns the remainder after the number is divided. The syntax of this Power BI DAX MOD Function is:
MOD(number, divisor)
For example MOD(5, 2) = 1.
Below statement return the remainders of Sales divisible by SQRT function resul
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 of this Power BI DAX MRound Function is:
MROUND(expression, multiple)
Statement that we used is:
MROUND = MROUND(EmployeeSales[Sales], 0.05)
Power BI DAX QUOTIENT Function
The DAX QUOTIENT function returns the integer portion of the division result. Syntax of Power BI DAX QUOTIENT Function is:
QUOTIENT(numerator, denominator)
Below statement returns the integer portion of Sales divided by Mod function result:
QUOTIENT = QUOTIENT(EmployeeSales[Sales], EmployeeSales[MOD])
Power BI DAX RAND Function
The DAX RAND function returns the random positive number between 0 and 1. The syntax of 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 returns the random number between user specified start and end values. The syntax of 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 DAX TRUNC function truncates the given number to an integer by removing the decimals. The syntax of Power BI DAX TRUNC Function is:
TRUNC(number, num_of_digits)
It will truncate the Sales column values
TRUNC = TRUNC(EmployeeSales[Sales], 1)
Power BI DAX ROUNDDOWN Function
The DAX ROUNDDOWN function round down the given number towards a zero. The syntax of Power BI DAX ROUNDDOWN Function is:
ROUNDDOWN(number, num_of_digits)
It will round down the Sales column values to 2 digits.
ROUNDDOWN = ROUNDDOWN(EmployeeSales[Sales], -2)
Power BI DAX ROUNDUP Function
The DAX ROUNDUP function round up the given number away from zero. The syntax of Power BI DAX ROUNDUP Function is:
ROUNDUP(number, num_of_digits)
Below statement round up the Sales column values to 2 digits (i.e., greater than original).
ROUNDUP = ROUNDUP(EmployeeSales[Sales], -2)
Power BI DAX LCM Function
The DAX LCM function is used to find the Least Common, or Lowest Common from multiple integer values. The syntax of this Power BI DAX LCM Function is:
LCM(expression1, expression2,.....)
It returns the Least Common from SQRT result, and MOD function result
LCM = LCM(EmployeeSales[SQRT], EmployeeSales[MOD])
Power BI DAX GCD Function
The DAX GCD function is used to find the Greatest Common Divisor from multiple integer values. The syntax of this Power BI DAX GCD Function is:
GCD(expression1, expression2,.....)
It returns the Greatest Common Divisor of SQRT function result, and MOD result
GCD = GCD(EmployeeSales[SQRT], EmployeeSales[MOD])
Leave a Reply