CEILING

Updated on
Rick de Groot

CEILING is a DAX function that rounds a number up to the nearest integer or to the nearest unit of significance.

Syntax

CEILING(
   Number,
   Significance
)
Argument Properties Description
Number The value you want to round.
Significance The multiple to which you want to round.

Return Values

A number rounded as specified.

Remarks

  • There are two CEILING functions in DAX, with the following differences:

    • The CEILING function emulates the behavior of the CEILING function in Excel.
    • The ISO.CEILING function follows the ISO-defined behavior for determining the ceiling value.

  • The two functions return the same value for positive numbers, but different values for negative numbers. When using a positive multiple of significance, both CEILING and ISO.CEILING round negative numbers upward (toward positive infinity). When using a negative multiple of significance, CEILING rounds negative numbers downward (toward negative infinity), while ISO.CEILING rounds negative numbers upward (toward positive infinity).

  • The return type is usually of the same type of the significant argument, with the following exceptions:

    • If the number argument type is currency, the return type is currency.
    • If the significance argument type is Boolean, the return type is integer.
    • If the significance argument type is non-numeric, the return type is real.

Example 1

The following formula returns 4.45. This might be useful if you want to avoid using smaller units in your pricing. If an existing product is priced at $4.42, you can use CEILING to round prices up to the nearest unit of five cents.

= CEILING(4.42,0.05)

Example 2

The following formula returns similar results as the previous example, but uses numeric values stored in the column, ProductPrice.

= CEILING([ProductPrice],0.05)

Other functions related to CEILING are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/ceiling-function-dax

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy