Power BI help users to visualize and interpret data easily and DAX Math Functions helps us to perform mathematical operations on data such as creating custom calculations for our reports and dashboards.
Sample Dataset: Library Supply Companies
You can download dataset from here.
Now go to the Power BI desktop to load the dataset from excel.
1. ABS Function
The ABS function returns the absolute value of a number.
Syntax:
ABS(<number>)
- number: The number (or column reference) from which we want to get the absolute value.
Example : Calculating the difference between the Sum of the Tax amount (INR) and the Sum of the Total amount (INR).
2. Ceiling Function
The CEILING function rounds a number up to the nearest integer or specified multiple. Itβs useful when dealing with quantities or pricing that needs to adhere to certain standards such as rounding to the nearest whole unit or predefined increment.
Syntax:
CEILING(<number>, <significance>)
- number: Number we want to round.
- significance: Multiple to which we want to round. For example, 1 to round to the nearest integer.
Example: To round 572369.5499999996 up to the nearest 0.01 i.e, 572369.55.
3. Convert Function
The CONVERT function converts an expression from one data type to another.
Syntax:
CONVERT(<Expression>, <Datatype>)
- Expression: Any valid DAX expression.
- Datatype: The data type to convert to such as INTEGER, DOUBLE, STRING, BOOLEAN, CURRENCY, DATETIME etc.
Example: This converts the Total amount (INR) values to the INTEGER data type.
4. Currency Function
The CURRENCY function evaluates an expression and returns its value as the Currency data type.
Syntax:
CURRENCY(<value>)
- value: A scalar value that needs to be converted to a currency format.
Example: To converts 23.44 to the Currency data type.
5. Degrees Function
The DEGREESfunction converts an angle from radians to degrees. This is useful when we need to work with angle measurements that are not in radians
Syntax:
DEGREES (angle)
- angle:Β The angle in radians that we want to convert to degrees.
Example: This converts the value of Pi (
6. Divide Function
The DIVIDE function divides two numbers and returns a result. If the denominator is zero, it returns a blank value (or an alternate result, if provided).
Syntax:
DIVIDE(<numerator>, <denominator>, [<alternate result>])
- numerator: Number we want to divide.
- denominator: Number by which we are dividing.
- alternate result: (Optional) A value returned when division by zero occurs.
Example: This divides 234 by 12 and returns the result.
7. Even Function
The EVENfunction rounds a number away from zero to the nearest even integer. This can be helpful for certain calculations such as grouping items in pairs.
Syntax:
EVEN(<number>)
- number: Number we want to round to the nearest even integer.
Example: This rounds 23.44 up to the nearest even number which is 24.
8. Exp Function
The EXP function returns the value of eraised to the power of the specified number. The constant e is approximately 2.71828 and is used in exponential growth and decay calculations.
Syntax:
EXP(<number>)
- number: The exponent to which the base e is raised.
Example: This returns the value of e raised to the power of 4.
9. Fact Function
The FACTfunction returns the factorial of a number which is the product of all positive integers up to that number. This is useful in combinatorics, statistics and probability.
Syntax:
FACT(<number>)
- number: The positive, non-negative number that we want to find the factorial for.
Example: This returns the factorial of 5 which is 120 (5 * 4 * 3 * 2 * 1).
10. Floor Function
The FLOOR function rounds a number toward zero to the nearest multiple of a specified significance. This is helpful when working with pricing or measurements.
Syntax:
FLOOR(<number>, <significance>)
- number: The number we want to round.
- significance: The multiple to which we want to round.
Example: This rounds 245.33 down to the nearest multiple of 0.3.
11. GCD Function
The GCD function returns the greatest common divisor of two or more integers. This is useful for simplifying fractions or finding the largest number that divides both numbers without a remainder.
Syntax:
GCD(<number1>, [<number2>, ...])
- number1: First number.
- number2: (Optional) Additional numbers for which we want to find the greatest common divisor.
Example: This returns the greatest common divisor of 122 and 4 which is 2.
12. INT Function
The INT function rounds a number down to the nearest integer. Unlike TRUNC, INT rounds negative numbers away from zero.
Syntax:
INT(<number>)
- number: Number we want to round down to the nearest integer..
Example: This rounds 55.44 down to 55.
13. Iso.Ceiling Function
The ISO.CEILING function behaves similarly to the CEILING function but follows ISO standards for rounding numbers. It rounds a number up to the nearest multiple of a specified significance.
Syntax:
ISO.CEILING(<number>, [<significance>])
- number: Number we want to round.
- significance: Multiple to which we want to round.
Example:Β This rounds 2.999 up to the nearest multiple of 0.2.
14. LCM Function
The LCM function returns the least common multiple of two or more integers. This is useful when working with fractions that have different denominators.
Syntax:
LCM(<number1>, [<number2>, ...])
- number1: First number.
- number2: (Optional) Additional numbers for which we want to find the least common multiple.
Example: This finds the least common multiple of 45 and 56.
15. LN Function
The LN function returns the natural logarithm of a number. The natural logarithm uses e (approximately 2.71828) as its base.
Syntax:
LN(<number>)
- number: A positive number for which you want to calculate the natural logarithm.
Example: This returns the natural logarithm of 0.004.
16. LOG Function
The LOG function returns the logarithm of a number to a specified base. If no base is provided, the function defaults to base 10. This function is useful when working with exponential data or scaling numbers in a logarithmic scale.
Syntax:
LOG(<number>, [<base>])
- number: Positive number for which we want to calculate the logarithm.
- base: Optional base for the logarithm. If omitted, base 10 is used.
Example: This returns the logarithm of 1035 to base 3.
17. LOG10 Function
The LOG10 function returns the base-10 logarithm of a number. Itβs typically used when we want to scale or normalize data on a logarithmic scale with a base of 10.
Syntax:
LOG10(<number>)
- number: AΒ positive number for which we want the base-10 logarithm.
Example: This returns the base-10 logarithm of 1035.
18. MOD Function
The MOD function returns the remainder after dividing a number by a divisor. It is useful when we need to calculate remainders or distribute items into groups.
Syntax:
MOD(<number>, <divisor>)
- number: The figure for which we need to calculate the divisional remainder.
- divisor: The number by which we divide the number.
Example: This returns the remainder when 1035 is divided by 5.
19. MROUND Function
The MROUNDfunction rounds a number to the nearest multiple of a specified value. This can be used to round prices, measurements or other values to a required standard.
Syntax:
MROUND(<number>, <multiple>)
- number: Number we want to round.
- multiple: The significant multiple that we want to round the number to.
Example: This rounds 455.6 to the nearest multiple of 5.
20. ODD Function
The ODD function rounds a number away from zero to the nearest odd integer. This can be helpful when dealing with objects or quantities that need to be in odd numbers.
Syntax:
ODD (number)
- number: Number we want to round to the nearest odd integer.
Example: This rounds 455.6 to the nearest odd number which is 457.
21. Pi Function
The PI function returns the mathematical constant Ο.
Syntax:
PI()
- No parameters required.
Example: This returns the value of Pi.
22. Power Function
The POWER function raises a base number to a given exponent. Itβs useful when we want to calculate exponential growth or other power-related operations.
Syntax:
POWER(<number>, <power>)
- number: Any real number can act as the base number.
- power: Higher exponent from the base number.
Example: This returns 5 raised to the power of 3 (125).
23. Quotient Function
The QUOTIENTfunction returns the integer part of a division. This is useful when we need to discard the remainder or fractional part from a division operation.
Syntax:
QUOTIENT(<numerator>, <denominator>)
- numerator: Division factor (or dividend).
- denominator: Number by which the numerator is divided.
Example: This returns the integer part of 234 divided by 3 (78).
24. Radians Function
The RADIANS function converts an angle from degrees to radians which is necessary for various mathematical and trigonometric functions that work with radians.
Syntax:
RADIANS (angle)
- angle: An angle that we want to convert from degrees to radians.
Example: This converts 90 degrees to radians (approximately 1.57).
25. Rand Function
This generates a random decimal number between 0 and 1. Itβs useful for simulations, random data generation or testing.
Syntax:
RAND ()
- No parameters required.
Example: This generates a random number between 0 and 1 (e.g 0.48).
26. RandBetween Function
The RANDBETWEENfunction generates a random integer between two specified values. It is useful when we need random integers for simulations or tests.
Syntax:
RANDBETWEEN(<bottom>, <top>)
- bottom: Smallest integer that will be returned by the function.
- top: Function will return the greatest possible integer.
Example: This generates a random integer between 23 and 44 i.e 31.
27. Round Function
The ROUNDfunction rounds a number to a specified number of digits. This is used in financial calculations to round prices, quantities or other data points to the nearest decimal place.
- The number is rounded to the specified number of decimal places if num digits are higher than zero.
- The number is rounded to the nearest integer if the num digit is 0.
- The number is rounded to the left of the decimal point if the num digits are less than 0.
Syntax:
ROUND(<number>, <num_digits>)
- number: Value we want to round.
- num_digits: Number of digits we want to round to. A value of zero rounds to the nearest integer.
Example: This rounds the value of Pi to the nearest whole number.
28. RoundDown Function
The ROUNDDOWN function rounds a number down to the nearest specified digit. Unlike ROUND which rounds based on the decimal it will always round down toward zero.
- The value in number is rounded down to the provided number of decimal places if the num digits are more than zero.
- The value in number is rounded to the nearest integer if the num digits are 0.
- The value in number is rounded to the left of the decimal point if the num digits are less than 0.
Syntax:
ROUNDDOWN(<number>, <num_digits>)
- number: Value to be rounded down.
- num_digits: Number of digits to round to.
Example: This rounds 23.44444 down to 23.40.
29. RoundUp Function
The ROUNDUPfunction rounds a number up, away from zero, to the specified number of digits. It is useful when we need to ensure values are always rounded up.
- The number is rounded up to the provided number of decimal places if the num digits are larger than 0 (zero).
- The number is rounded up to the nearest integer if the num digit is 0.
- The number is rounded to the left of the decimal point if the num digits are less than 0.
Syntax:
ROUNDUP(<number>, <num_digits>)
- number: Value to be rounded up.
- num_digits: Number of digits to round to.
Example: This rounds 23.44444 up to 23.50.
30. Sign Function
The SIGNfunction identifies the sign of a number. It returns 1 for positive numbers, 0 for zero and -1 for negative numbers.
Syntax:
SIGN(<number>)
- number: A real number or an expression that evaluates to a number.
Example: This returns 1 because the number is positive.
31. Sqrt Function
The SQRTfunction returns the square root of a number. This is helpful when working with geometric calculations or any situation requiring square roots.
Syntax:
SQRT(<number>)
- number: Number for which we want to calculate the square root.
Example: This returns the square root of 23.44444.
32. SqrtPi Function
The SQRTPI function returns the square root of the product of a number and Pi. This is useful for certain geometric and statistical calculations.
Syntax:
SQRTPI (number)
- number: Value to be multiplied by Pi before taking the square root.
Example: This returns the square root of 23.44444 Γ Ο, approximately 8.58.
33. Trunc Function
The TRUNC function removes the decimal portion of a number, truncating it to the specified number of digits. It is useful when we need to eliminate fractional values.
Syntax:
TRUNC(<number>, <num_digits>)
- number: Value we want to truncate.
- num_digits: Number of digits to which we want to truncate. If num_digits is omitted, the default is 0, so the fractional portion is removed.
Example: This truncates 45.0000345 to 6 decimal places (45.00).
By mastering these DAX Math Functions we can enhance our data analysis capabilities in Power BI which helps in more precise calculations and better insights.
