Daily DAX : Day 181 ROUNDDOWN

 The ROUNDDOWN function in Power BI's DAX (Data Analysis Expressions) language is used to round a number down to a specified number of decimal places, always toward zero. Unlike the ROUND function, which rounds based on standard rounding rules (up or down depending on the value), ROUNDDOWN truncates the number to the specified precision without considering whether the next digit would cause it to round up.

Syntax

dax

ROUNDDOWN(number, num_digits)

    number: The number you want to round down.

    num_digits: The number of decimal places to round down to.

        If num_digits is positive, it specifies the number of decimal places.

        If num_digits is zero, it rounds down to the nearest integer.

        If num_digits is negative, it rounds down to the nearest multiple of 10, 100, etc., based on the value (e.g., -1 rounds to the nearest 10, -2 to the nearest 100).

Return Value

The function returns the number rounded down to the specified number of digits.

Examples

    Basic Decimal Rounding:

    dax

    ROUNDDOWN(3.14159, 2)

    Result: 3.14

        Rounds down to two decimal places, truncating the remaining digits.

    Integer Rounding:

    dax

    ROUNDDOWN(123.456, 0)

    Result: 123

        Rounds down to zero decimal places, effectively removing the fractional part.

    Negative Digits:

    dax

    ROUNDDOWN(1234.56, -1)

    Result: 1230

        Rounds down to the nearest 10.

    Negative Numbers:

    dax

    ROUNDDOWN(-3.14159, 2)

    Result: -3.14

        Rounds down toward zero, keeping two decimal places.

Use Cases

    Financial Calculations:

        When calculating taxes, discounts, or fees, businesses may need to round down to avoid overcharging customers. For example, ensuring a tax amount doesn’t exceed the exact calculated value:

        dax

        ROUNDDOWN([TotalPrice] * 0.075, 2)

        This ensures the tax is rounded down to two decimal places for precision.

    Inventory Management:

        When determining how many whole units can be allocated from a fractional quantity, ROUNDDOWN is useful:

        dax

        ROUNDDOWN([AvailableStock] / [UnitsPerBox], 0)

        This calculates how many full boxes can be packed without overestimating.

    Budgeting and Forecasting:

        For conservative estimates, ROUNDDOWN can be used to avoid overestimating revenue or resources:

        dax

        ROUNDDOWN([ProjectedRevenue], -2)

        This rounds down revenue projections to the nearest hundred for cautious planning.

    Data Cleaning:

        When preparing data for reporting, ROUNDDOWN can standardize numerical values to a consistent number of decimal places, ensuring uniformity in visualizations or tables.

Notes

    Difference from ROUND: Unlike ROUND, which might round up (e.g., ROUND(3.145, 2) gives 3.15), ROUNDDOWN always truncates downward (e.g., ROUNDDOWN(3.145, 2) gives 3.14).

    Handling Nulls: If the input number is null, ROUNDDOWN returns null.

    Performance: ROUNDDOWN is a simple function and has minimal impact on calculation performance, making it suitable for large datasets.

Practical Example in Power BI

Suppose you have a table with sales data, and you want to calculate the number of complete product bundles that can be sold from available inventory. You could create a calculated column like this:

dax

CompleteBundles = ROUNDDOWN('Sales'[Inventory] / 'Sales'[UnitsPerBundle], 0)

If Inventory is 25 units and UnitsPerBundle is 6, the result is 4 (since 25 ÷ 6 = 4.166, rounded down to 4 complete bundles).

This function is particularly valuable when precision and conservative estimates are critical in your analysis.