Problem
In Power BI, there are some DAX functions which are used to round values based on requirements. But in my experience, I have seen that these are among the least used DAX functions in Power BI as there are other ways to round values other than using DAX function.
As mentioned above, although there are other ways to round values in Power BI without using DAX, but these DAX functions are there for a reason. There are instances where the alternative way of rounding values cannot be used to round values in an expression in DAX, hence the purpose of this article.
Solution
Let’s start by looking at the various (common) rounding functions we have in DAX currently and use a scenario to demonstrate how and when each can be used by first describing the scenario as follows.
Let’s take a typical scenario where you need to create a measure for Average SalesAmount for the dataset below (Note: You can use any other aggregation depending on your business requirement, the use of Average here is for demo purpose only).
Your measure calculation would look something like the one below.
= Average SalesAmount = AVERAGE(Data[SalesAmount])
If we decide to show the output of this measure by month and year on a table visual, we should get something as below.
What if the business requirement expects to see the output with a text value like “per mth” or “points” or “Avg. Per Month” at the end of each monthly row value on the table? For instance, for Jan 2011, we want to see “3,262.67 per mth“. Let’s try concatenating the text value to the initial DAX expression we created earlier and see.
= Average SalesAmount = AVERAGE(Data[SalesAmount])& " per mth"
The new output should look something as follows.
As you can see, we have the values with elongated decimal places (but our expectation is a simple two decimal places), and you would not be able to use the rounding buttons on the Power BI Desktop ribbon or format pane to do this since the values would automatically become text values. This is where DAX Rounding functions can become useful.
Thus, let’s look at each DAX Rounding functions and how they can be used to achieve this case scenario.
DAX ROUND Function
This DAX function rounds values (numbers) to the number of digits specified and returns a decimal number.
Its syntax is as seen below.
= ROUND(<number>, <num_digits>)
- <number>: This refers to the number you want to round.
- <num_digits>: This is the number of digits that you want to round to.
You can read more about the DAX ROUND function as documented in the Microsoft Documentation.
Let’s now see how we can use the DAX ROUND function to solve the case scenario we have been using as a demo by creating a new measure as follows.
Average SalesAmount (ROUND) = ROUND(AVERAGE(Data[SalesAmount]),2)& " per mth"
The <number> in the above expression is the “AVERAGE(Data[SalesAmount])” part of it, and the <num_digits> is “2“. The output of using this function is as seen below.
DAX ROUNDDOWN Function
This DAX function rounds a number down towards zero and returns a decimal number.
Its syntax is as seen below.
= ROUNDDOWN(<number>, <num_digits>)
- <number>: This refers to a real number you want to be rounded down.
- <num_digits>: This is the number of digits that you want to round down to.
You can read more about the DAX ROUNDDOWN function as documented in the Microsoft Documentation.
Let’s look at how we can use DAX ROUNDDOWN function to solve the case scenario we have been using as a demo by creating a new measure as follows.
Average SalesAmount (ROUNDDOWN) = ROUNDDOWN(AVERAGE(Data[SalesAmount]),2)& " per mth"
Like in the ROUND function, The <number> in the above expression is the “AVERAGE(Data[SalesAmount])” part of it, and the <num_digits> is “2“. The output of using this function is as seen below.
DAX FLOOR Function
This DAX function rounds a number down to the nearest multiple of significance towards zero and returns a decimal number.
Its syntax is as seen below.
= FLOOR(<number>, <significance>)
- <number>: This refers to a number you want to be rounded down.
- <significance>: This is the multiple to which you want to round.
You can read more about the DAX FLOOR function as documented in the Microsoft Documentation.
Let’s look at how we can use DAX FLOOR function to solve the case scenario we have been using as a demo by creating a new measure as follows.
Average SalesAmount (FLOOR) = FLOOR(AVERAGE(Data[SalesAmount]),0.01)& " per mth"
Like in the ROUND and ROUNDDOWN function, The <number> in the above expression is the “AVERAGE(Data[SalesAmount])” part of it, and the <significance> is “0.01” because we require a two decimal place output. The output of using this function is as seen below.
You would find out that the outputs of the FLOOR and ROUNDDOWN functions are similar (thus you might be able to use either in same business scenario). The only notable difference is in the arguments in the formula.
DAX TRUNC Function
The DAX TRUNC function removes the integer or fraction part of a number and rounds the number to an integer and usually returns a whole number, but you can also specify it to return a decimal number as would be shown in this demo.
Its syntax is as seen below.
= TRUNC(<number>, <num_digits>)
- <number>: This refers to a number you want to be rounded by truncation.
- <num_digits>: This is the number specifying the precision of the truncation, and its usually Zero by default.
You can read more about the DAX TRUNC function as documented in the Microsoft Documentation.
Let’s look at how we can use the DAX TRUNC function to solve the case scenario we have been using as a demo by creating a new measure as follows.
Average SalesAmount (TRUNC) = TRUNC(AVERAGE(Data[SalesAmount]),2)& " per mth"
Like in the other functions earlier discussed, The <number> in the above expression is the “AVERAGE(Data[SalesAmount])” part of it, and the <num_digits> is “2” because we require a two decimal place output.
Note: But if you do not include the second argument (<num_digits> part) the return value would just be a whole number.
The output of using this function with a <num_digits> argument is as seen below.
You would find out that the outputs of the TRUNC, FLOOR and ROUNDDOWN functions are similar (thus you might be able to use either in same business scenario), but this is only when you use the second argument in the TRUNC DAX function.
DAX MROUND Function
This DAX function returns a decimal number which has been rounded to the desired multiple.
Its syntax is as seen below.
= MROUND(<number>, <multiple>)
- <number>: This refers to a number you want to be rounded.
- <multiple>: This is the multiple of significance to which you want the number rounded to.
You can read more about the DAX MROUND function as documented in the Microsoft Documentation.
Let’s look at how we can use DAX MROUND function to solve the case scenario we have been using as a demo by creating a new measure as follows.
Average SalesAmount (MROUND) = MROUND(AVERAGE(Data[SalesAmount]),0.01)& " per mth"
Like in the other rounding functions described earlier, the <number> in the above expression is the “AVERAGE(Data[SalesAmount])” part of it, and the <multiple> is “0.01” because we require a two decimal place output. If you require more than two decimal place outputs in your solution just add an additional “0” (i.e., “0.001” for a three decimal place output) The output of using this function is as seen below.
You would find that the outputs of the MROUND and that of ROUND functions are similar, hence they could be used in place of each other.
DAX CEILING Function
This DAX function rounds a number up to the nearest multiple of significance or to the nearest integer and returns a number.
Its syntax is as seen below.
= CEILING(<number>, <significance>)
- <number>: This refers to a number you want to be rounded.
- <significance>: This is the multiple of significance to which you want the number rounded to.
You can read more about the DAX CEILING function as documented in the Microsoft Documentation.
Let’s look at how we can use DAX CEILING function to solve the case scenario we have been using as a demo by creating a new measure as follows.
Average SalesAmount (CEILING) = CEILING(AVERAGE(Data[SalesAmount]),0.01)& " per mth"
The <number> in the above expression is the “AVERAGE(Data[SalesAmount])” part of it, and the <significance> is “0.01” because we require a two decimal place output. If you require more than two decimal place outputs in your solution just add an additional “0” (i.e., “0.001” for a three decimal place output). The output of using this function is as seen below.
DAX ROUNDUP Function
The DAX ROUNDUP function returns a decimal number which has been rounded up away from zero.
Its syntax is as seen below.
= ROUNDUP(<number>, <num_digits>)
- <number>: This refers to a number you want to be rounded up.
- <num_digits>: This is the number of digits to which you want the real number rounded to.
You can read more about the DAX ROUNDUP function as documented in the Microsoft Documentation.
Let’s look at how we can use DAX ROUNDUP function to solve the case scenario we have been using as a demo by creating a new measure as follows.
Average SalesAmount (ROUNDUP) = ROUNDUP(AVERAGE(Data[SalesAmount]),2)& " per mth"
Like in the other rounding functions described earlier, the <number> in the above expression is the “AVERAGE(Data[SalesAmount])” part of it, and the <num_digits> is “2” because we require a two decimal place output. The output of using this function is as seen below.
DAX ISO.CEILING Function
This DAX function rounds up a number to the nearest multiple of significance or to the nearest integer and returns a number that is of the same type as the <number> argument which has been rounded as specified.
Its syntax is as seen below.
= ISO.CEILING(<number>[, <significance>])
- <number>: This refers to a value you want rounded.
- <significance>: This refers to the multiple of significance to which you want to round. This argument is optional.
The ISO.CEILING is very similar in output. The main difference between the two is in their behaviour and what values they return for positive and negative values. You can read more about the DAX ROUNDUP function as documented in the Microsoft Documentation.
Let’s look at how we can use DAX ISO.CEILING function to solve the case scenario we have been using as a demo by creating a new measure as follows.
Average SalesAmount (ISO.CEILING) = ISO.CEILING(AVERAGE(Data[SalesAmount]), 0.01)& " per mth"
Like in the other rounding functions described earlier, the <number> in the above expression is the “AVERAGE(Data[SalesAmount])” part of it, and the optional <significance> is “0.01” because we require a two decimal place output. The output of using this function is as seen below.
Apart from the DAX rounding functions already discussed in this article, there are other DAX rounding functions that can be used to round values, but I have not described them in detail in this article as they behave in a different way from the others already described. Some of these DAX functions include:
- The DAX INT function: This function rounds a number down to the nearest integer and returns a whole number. The syntax below is seen to have only one argument unlike the others we already described.
= INT(<number>) - The DAX EVEN function: This DAX function evaluates and returns values rounded to the nearest even integers. Again, unlike the other rounding functions already described, this function has only one argument as seen in the syntax below.
= EVEN(<number>) - The DAX ODD function: This DAX function is the direct opposite of the EVEN function as the former rounds values up to the nearest odd integers. Like EVEN and INT, the ODD function only has one argument as seen below.
= ODD(<number>)
In summary, in this article, we have successfully demonstrated a business scenario where DAX rounding functions are best suited to provide the desired solution we wanted. It would be great to hear from anyone how they have used the DAX rounding functions in their business to provide solutions.
Next Steps
- A detailed Microsoft Documentation on all Math and Trig functions can be found here.
- You can read more on using the DAX INT function here.
- You can read more on using the DAX ODD function here.
- You can read more on using the DAX EVEN function here.
- Try this tip out in your own data as business requires.
