How to Do Custom Rounding in DAX

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!