Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs
    • SQL FAQ’s

Power BI DAX Math Functions

by suresh

How to use Power BI DAX Math Functions with examples?. Microsoft Power BI DAX provides various Math or mathematical Functions such as CEILING, FLOOR, CURRENCY, INT, SQRT, POWER, MOD, DIVIDE, ROUND, ROUNDUP, ROUNDDOWN, LCM, GCD, etc.

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 1

Power BI DAX Math Functions

The following series of examples show you the use of DAX Math Functions in Power BI

Power BI DAX CEILING Function

The DAX Ceiling function in Power BI returns the 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 in Power BI, we have to use the calculated column. In order to create a column, please click on the New Column option under the Power BI Home tab, or Modeling tab.

Power BI DAX CEILING Function 2

As you can see from the below screenshot, we renamed the default column name as CEIL. The below statement find the closest integer value greater than or equal to Sales

CEILING(EmployeeSales[Sales])
Power BI DAX CEILING Function 3

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 CEILING Function 4

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 of this Power BI DAX Floor Function is:

FLOOR(expression, significance)

This Power BI DAX math function finds the closest value which is less than or equal to Sales

FLOOR(EmployeeSales[Sales], 1)
POWER BI DAX FLOOR function 5

Power BI DAX ROUND Function

The Power BI DAX ROUND function is to round the given values to the closest value. The syntax of this Power BI DAX Round Function is:

ROUND(expression, significance)

It rounds the Sales values to the nearest integer

ROUND = ROUND(EmployeeSales[Sales], 0)
Power BI DAX ROUND function 6

Power BI DAX CURRENCY Function

The DAX CURRENCY function in Power BI converts the value into the currency data type. The syntax of this Power BI DAX CURRENCY Function is:

CURRENCY(expression)

It converts the Sales values to Currency data type

Money = CURRENCY(EmployeeSales[Sales])
Power BI DAX CURRENCY function 7

Power BI DAX INT Function

The DAX INT function in Power BI converts the given value into an integer data type. The syntax of this Power BI DAX INT Function is:

INT(expression)

Power BI DAX INT function converts the Sales values to Integer data type

INT = INT(EmployeeSales[Sales])
Power BI DAX INT function 8

Power BI DAX SQRT Function

The DAX SQRT function in power BI returns the square root of a given number. The 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 SQRT function 9

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 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 SIGN function 10

Power BI DAX EVEN Function

The DAX EVEN function in Power BI returns the nearest even integer of a given number. The syntax of this Power BI DAX EVEN Function is:

EVEN(expression)

The below statement returns the nearest even integer of a Service Grade column values

EVEN = EVEN(EmployeeSales[Service Grade])
Power BI DAX EVEN function 11

Power BI DAX ODD Function

The Power BI 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 ODD function 12

Power BI DAX FACT Function

The Power BI DAX FACT function finds 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

The below statement returns the factorial of a yearly income divide by 1000

FACT = FACT(EmployeeSales[Yearly Income] / 1000)
Power BI DAX FACT function 13

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 of this Power BI DAX POWER Function is:

POWER(expression, raise_number)

For example Power(4, 3) = 4³.

The below statement returns the factorial of a yearly income divide by 1000

POWER = POWER(EmployeeSales[Sales], 2)
Power BI DAX POWER function 14

Power BI DAX DIVIDE Function

The DAX DIVIDE function in Power BI 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)

The below statement divides sales column values by 30

POWER = POWER(EmployeeSales[Sales], 2)
Power BI DAX DIVIDE function 15

Power BI DAX DIVIDE Function also accepts the third argument. Use this argument to specify the alternative number for BLANK values.

The below statement divides sales column values by Grade column values. If the result is blank, then 9 returned.

POWER = POWER(EmployeeSales[Sales], 2)
Power BI DAX DIVIDE function 16

Power BI DAX ABS Function

The Power BI 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 ABS function 17

Power BI DAX MOD Function

The DAX MOD function in Power BI returns the remainder after the number divided. The syntax of this Power BI DAX MOD Function is:

MOD(number, divisor)

For example MOD(5, 2) = 1.

The below statement returns the remainders of Sales divisible by the SQRT function result.

MOD = MOD(EmployeeSales[Sales], EmployeeSales[SQRT])
Power BI DAX MOD function 18

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)

The statement that we used is:

MROUND = MROUND(EmployeeSales[Sales], 0.05)
Power BI DAX MROUND function 19

Power BI DAX QUOTIENT Function

The DAX QUOTIENT function in Power BI returns the integer portion of the division result. The syntax of the Power BI DAX QUOTIENT Function is:

QUOTIENT(numerator, denominator)

The below statement returns the integer portion of Sales divided by Mod function result:

QUOTIENT = QUOTIENT(EmployeeSales[Sales], EmployeeSales[MOD])
Power BI DAX QUOTIENT function 20

Power BI DAX RAND Function

The Power BI DAX RAND function returns the random positive number between 0 and 1. The syntax of the Power BI DAX RAND Function is:

RAND()

Let me create a new column to generate random values

RAND = RAND()
Power BI DAX RAND function 21

Power BI DAX RANDBETWEEN Function

The DAX RANDBETWEEN function in Power BI returns the random number between the user-specified start and end values. The syntax of 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 RANDBETWEEN function 22

Power BI DAX TRUNC Function

The Power BI DAX TRUNC function truncates the given number to an integer by removing the decimals. The syntax of 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)
Power BI DAX TRUNC function 23

Power BI DAX ROUNDDOWN Function

The Power BI DAX ROUNDDOWN function rounds down the given number towards a zero. The syntax of the Power BI DAX ROUNDDOWN Function is:

ROUNDDOWN(number, num_of_digits)

It rounds down the Sales column values to 2 digits.

ROUNDDOWN = ROUNDDOWN(EmployeeSales[Sales], -2)
Power BI DAX ROUNDDOWN function 24

Power BI DAX ROUNDUP Function

The Power BI DAX ROUNDUP function rounds up the given number away from zero. The syntax of the Power BI DAX ROUNDUP Function is:

ROUNDUP(number, num_of_digits)

The below statement rounds up the Sales column values to 2 digits (i.e., greater than original).

ROUNDUP = ROUNDUP(EmployeeSales[Sales], -2)
Power BI DAX ROUNDUP function 25

Power BI DAX LCM Function

The DAX LCM function finds 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 LCM function 26

Power BI DAX GCD Function

The Power BI DAX GCD function finds the Greatest Common Divisor from multiple integer values. The Power BI DAX GCD Function syntax is:

GCD(expression1, expression2,.....)

This Power BI DAX math function returns the Greatest Common Divisor of SQRT function result and MOD result

GCD = GCD(EmployeeSales[SQRT], EmployeeSales[MOD])
Power BI DAX GCD function 27

Placed Under: Power BI

  • Install Power BI Desktop
  • Connect Power BI to SQL Server
  • Add Rename and Duplicate Pages
  • Connect to Multiple Excel Sheets
  • Get Excel Data to Power BI
  • Get Text File Data to Power BI
  • Load Data from Multiple Sources
  • Remove Power BI Table Columns
  • Create Power BI Bins
  • Power BI Joins
  • Change Column Data Types
  • Combine Multiple Tables
  • Power BI Clusters
  • How to Enter Data into Power BI
  • How to Format Dates in Power BI
  • Create Power BI Groups
  • Create Power BI Query Groups
  • Create Power BI Hierarchy
  • Power BI Pivot Table
  • Power BI Unpivot Table
  • Rename Power BI Table Names
  • Rename power bi column names
  • Split Columns in Power BI
  • Remove reorder power bi column
  • Power BI Area Chart
  • Format Power BI Area Chart
  • Power BI Bar Chart
  • Format Power BI Bar Chart
  • Power BI Clustered Bar Chart
  • Clustered Column Chart
  • Create a Power BI Card
  • Format Power BI Card
  • Create Power BI Multi-Row Card
  • Format Power BI Multi-Row Card
  • Power BI Column Chart
  • Format Power BI Column Chart
  • Power BI Donut Chart
  • Format Power BI Donut Chart
  • Power BI Funnel Chart
  • Format Power BI Funnel Chart
  • Power BI Heat Map
  • Power BI Line Chart
  • Format Power BI Line Chart
  • Line & Clustered Column Chart
  • Format Line & Clustered Column
  • Line & Stacked Column Chart
  • Format Line & Stacked Column
  • Power BI Matrix
  • Format Power BI Matrix
  • Power BI Table
  • Format Power BI Table
  • Add Data Bars to Power BI Table
  • Add alternate Table Row Colors
  • Power BI Pie Chart
  • Format Power BI Pie Chart
  • Power BI Ribbon Chart
  • Format Power BI Ribbon Chart
  • Power BI Treemap
  • Format Power BI TreeMap
  • Power BI Scatter Chart
  • Format Power BI Scatter Chart
  • Power BI Stacked Area Chart
  • Power BI Stacked Bar Chart
  • Format Stacked Bar Chart
  • Power BI Stacked Column Chart
  • Format Stacked Column Chart
  • Power BI Waterfall Chart
  • Format Power BI Waterfall Chart
  • Power BI 100% Stacked Bar Chart
  • 100% Stacked Column Chart
  • Create a Power BI Map
  • Format Power BI Map
  • Power BI Filled Map
  • Format Power BI Filled Map
  • Power BI Basic Filters
  • Power BI Slicer
  • Power BI Top 10 Filters
  • Power BI Drill through Filters
  • Power BI Filters on Measures
  • Power BI Advanced Filters
  • Power BI Report Level Filters
  • Power BI Page Level Filters
  • Power BI Calculated Columns
  • Add Conditional Column
  • Create Power BI Custom Column
  • Power BI Calculated Measures
  • Power BI Calculated Tables
  • Power BI DAX String Functions
  • Power BI DAX Math Functions
  • DAX Trigonometric Functions
  • Power BI DAX Date Functions
  • Power BI DAX Logical Functions
  • Power BI DAX Agg. Functions
  • Power BI Dashboard Introduction
  • Power BI Dashboard Settings
  • Power BI Dashboard Actions
  • Delete Power BI Dashboard
  • Subscribe Power BI Dashboard
  • Add Web Content to Dashboard
  • Add Image to Dashboard
  • Add video to power bi dashboard
  • Add Title to Power BI Dashboard
  • Pin Workbooks to Dashboard
  • Register to Power BI Service
  • Connect to Power BI Service
  • Create Power BI Workspace
  • Share Power BI Workspace
  • Create a Report in Workspace
  • Rename a Report in Workspace
  • Rename a Dataset in Workspace
  • Quick insights in Power BI
  • Get insights in Power BI
  • Publish power bi desktop report
  • Upload Power BI Workbooks
  • Upload Excel Files to Dashboard
  • Edit Power BI App
  • View Published Power BI App
  • Publish Power BI App
  • Share Power BI Dashboard
  • Share Power BI Workspace
  • Share Power BI Report
  • Power BI Q&A
  • Power BI Q&A Advanced
  • Customize Q&A Suggestions
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy