{"id":1645,"date":"2023-01-21T03:02:59","date_gmt":"2023-01-21T03:02:59","guid":{"rendered":"https:\/\/monocroft.com\/?p=1645"},"modified":"2023-03-16T04:39:38","modified_gmt":"2023-03-16T04:39:38","slug":"how-to-do-custom-rounding-in-dax","status":"publish","type":"post","link":"https:\/\/monocroft.com\/how-to-do-custom-rounding-in-dax\/","title":{"rendered":"How to Do Custom Rounding in DAX"},"content":{"rendered":"\n
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.<\/p>\n\n\n\n
You can do custom rounding in DAX by using the FLOOR<\/strong> and CEILING<\/strong> functions. The CEILING function rounds a number up<\/strong> to the nearest integer or the nearest multiple of significance.<\/p>\n\n\n\n While the FLOOR function rounds a number down<\/strong>, toward zero, or to the nearest multiple of significance.<\/p>\n\n\n\n In this article, I will explain how to use both the floor and ceiling functions to do custom rounding in DAX.<\/p>\n\n\n\n Let’s get started.<\/p>\n\n\n\n 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. <\/p>\n\n\n\n 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.<\/p>\n\n\n\n To use the Where number<\/strong> is the decimal number you want to round down, and significance<\/strong> is the multiple to which you want to round down.<\/p>\n\n\n\n For example, if you want to round the number 3.5 down<\/strong> to the nearest whole number, you would use the formula:<\/p>\n\n\n\n This would return the value “3<\/strong>“<\/p>\n\n\n\n To round up a number using the Where number<\/strong> is the decimal number you want to round up, and significance<\/strong> is the multiple to which you want to round up.<\/p>\n\n\n\n For example, to round the number 3.5 up<\/strong> to the nearest whole number, your formula would be:<\/p>\n\n\n\n This would return the value “4”<\/strong><\/p>\n\n\n\n Alternatively, DAX provides the To use the Where number<\/strong> is the number you want to round, and decimal_places<\/strong> is the number of decimal places to which you want to round.<\/p>\n\n\n\n For example, if you want to round the number 4.56789 to two decimal places<\/strong>, you would use the formula:<\/p>\n\n\n\n This would return the value “4.57”<\/strong><\/p>\n\n\n\n To use the Where number is the number you want to round up, and decimal_places<\/strong> is the number of decimal places to which you want to round up.<\/p>\n\n\n\n For example, to round the number 4.567 up to the nearest whole number<\/strong>, you would use the formula:<\/p>\n\n\n\n This would return the value “5”<\/strong><\/p>\n\n\n\n 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.<\/p>\n\n\n\n For example, to round the number 12.8 to the nearest multiple of 5, you would use the formula:<\/p>\n\n\n\n This would return the value “15”<\/strong><\/p>\n\n\n\n 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.<\/strong><\/p>\n\n\n\n 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.<\/p>\n\n\n\n Thanks for reading.<\/p>\n\n\n\n I hope you enjoyed reading this article.<\/p>\n\n\n\n Happy analyzing!<\/p>\n","protected":false},"excerpt":{"rendered":" In Power BI, DAX provides a ROUND function that can be used to round off numbers to a certain number … <\/p>\nUnderstand How Custom Rounding Works in DAX<\/h2>\n\n\n\n
1. The FLOOR function<\/h2>\n\n\n\n
FLOOR <\/code><\/strong>function, you can write a formula like this:<\/p>\n\n\n\n
= FLOOR(number, significance)<\/code><\/strong><\/pre>\n\n\n\n
= FLOOR(3.5, 1)<\/code><\/strong><\/pre>\n\n\n\n
2. The CEILING function<\/h2>\n\n\n\n
CEILING <\/strong><\/code>function, you can write a formula like this:<\/p>\n\n\n\n
= CEILING(number, significance)<\/strong><\/code><\/pre>\n\n\n\n
= CEILING(3.5, 1)<\/strong><\/code><\/pre>\n\n\n\n
3. The ROUND and ROUNDUP functions<\/h2>\n\n\n\n
ROUND <\/code><\/strong>and
ROUNDUP<\/strong> <\/code>functions for rounding numbers into a specified number of decimal places. Here is how to use both:<\/p>\n\n\n\n
The ROUND function<\/h3>\n\n\n\n
ROUND <\/code><\/strong>function, you can use the formula:<\/p>\n\n\n\n
= ROUND(number, decimal_places)<\/strong><\/code><\/pre>\n\n\n\n
= ROUND(4.56789, 2)<\/strong><\/code><\/pre>\n\n\n\n
The ROUNDUP function<\/h3>\n\n\n\n
ROUNDUP<\/strong> <\/code>function, you can write a formula like this:<\/p>\n\n\n\n
= ROUNDUP(number, decimal_places)<\/strong><\/code><\/pre>\n\n\n\n
= ROUNDUP(4.567, 0)<\/strong><\/code><\/pre>\n\n\n\n
Custom rounding to a multiple<\/h2>\n\n\n\n
= ROUND(12.8\/5,0)*5<\/code><\/strong><\/pre>\n\n\n\n
Conclusion: How to Do Custom Rounding in DAX<\/h2>\n\n\n\n