In Power BI, DAX provides a ROUND function that can be used to round off numbers to a certain number of decimal places. However, if you want to do custom rounding, you will need to use a combination of other DAX functions.
You can do custom rounding in DAX by using the FLOOR and CEILING functions. The CEILING function rounds a number up to the nearest integer or the nearest multiple of significance.
While the FLOOR function rounds a number down, toward zero, or to the nearest multiple of significance.
In this article, I will explain how to use both the floor and ceiling functions to do custom rounding in DAX.
Let’s get started.
Understand How Custom Rounding Works in DAX
When writing DAX formulas, rounding is decided by a user-defined precision parameter. This parameter specifies the number of significant digits that will be returned and is adjusted based on the original value in the data expression.
Hence, understanding this will help you tailor your custom rounding to get the exact results you want. With this knowledge, let’s explore different functions to do custom rounding in Power BI.
1. The FLOOR function
To use the FLOOR
function, you can write a formula like this:
= FLOOR(number, significance)
Where number is the decimal number you want to round down, and significance is the multiple to which you want to round down.
For example, if you want to round the number 3.5 down to the nearest whole number, you would use the formula:
= FLOOR(3.5, 1)
This would return the value “3“
2. The CEILING function
To round up a number using the CEILING
function, you can write a formula like this:
= CEILING(number, significance)
Where number is the decimal number you want to round up, and significance is the multiple to which you want to round up.
For example, to round the number 3.5 up to the nearest whole number, your formula would be:
= CEILING(3.5, 1)
This would return the value “4”
3. The ROUND and ROUNDUP functions
Alternatively, DAX provides the ROUND
and ROUNDUP
functions for rounding numbers into a specified number of decimal places. Here is how to use both:
The ROUND function
To use the ROUND
function, you can use the formula:
= ROUND(number, decimal_places)
Where number is the number you want to round, and decimal_places is the number of decimal places to which you want to round.
For example, if you want to round the number 4.56789 to two decimal places, you would use the formula:
= ROUND(4.56789, 2)
This would return the value “4.57”
The ROUNDUP function
To use the ROUNDUP
function, you can write a formula like this:
= ROUNDUP(number, decimal_places)
Where number is the number you want to round up, and decimal_places is the number of decimal places to which you want to round up.
For example, to round the number 4.567 up to the nearest whole number, you would use the formula:
= ROUNDUP(4.567, 0)
This would return the value “5”
Custom rounding to a multiple
You can also use custom rounding to round to a certain multiple. This can be done by dividing the number by the multiple and then multiplying it by the multiple again.
For example, to round the number 12.8 to the nearest multiple of 5, you would use the formula:
= ROUND(12.8/5,0)*5
This would return the value “15”
Conclusion: How to Do Custom Rounding in DAX
Custom rounding in DAX allows you to round a number to a specific decimal place or to a certain multiple, which can be useful in a variety of situations such as formatting for visual effects, simplifying calculations, improving data analysis, etc.
However, kindly note that custom rounding can lead to accuracy issues. Thus, it is important to understand the implications of rounding on your data analysis results.
Thanks for reading.
I hope you enjoyed reading this article.
Happy analyzing!