Dax Function: ROUNDDOWN

Category: Mathematical and Trigonometric Functions

The ROUNDDOWN function in Power BI is a DAX function used to round a number down towards zero, regardless of the digit following the rounding position. Unlike the standard ROUND function, which uses mathematical rounding rules, ROUNDDOWN ensures that the result is always lower or equal to the original value (in absolute terms).

Purpose:

  • To truncate decimal places without considering the digit following the rounding position.
  • To ensure consistent downward rounding in financial and numerical analysis.

Type of Calculations:

  • Rounds numbers by removing extra decimal places or truncating digits to the left of the decimal point.
  • It disregards standard rounding rules, always rounding towards zero.

Practical Use Cases:

  1. Financial Analysis: Truncate excess decimals in budget or forecast calculations.
  2. Logistics: Calculate quantities where only whole numbers or conservative estimates are required.
  3. Error Prevention: Avoid overestimating values in critical computations.

ROUNDDOWN(<number>, <num_digits>)</num_digits></number>
ParameterTypeDescription
numberScalarThe numeric value or expression to round down.
num_digitsScalarSpecifies the number of digits to retain. Can be positive, zero, or negative.

 

How Does ROUNDDOWN Dax Function Works?

The ROUNDDOWN function removes digits based on the value of num_digits:

  1. For positive values of num_digits, the function truncates decimals without considering the following digit.
  2. For zero, it removes all decimal places.
  3. For negative values, it truncates digits to the left of the decimal point (e.g., to the nearest tens or hundreds).

Example Formula:

 Rounded Value = ⌊ Original Value × 10num_digits ⌋÷ 10num_digits 

Where ⌊x⌋ represents rounding down.


What Does It Return?

The ROUNDDOWN function returns a numeric value that has been rounded down towards zero based on the specified number of digits:

  • Positive num_digits: Truncates decimal places.
  • Zero num_digits: Rounds down to the nearest integer.
  • Negative num_digits: Rounds down to the left of the decimal point.

When Should We Use It?

  • Truncating Excess Decimals: When exact values are required without rounding up.
  • Conservative Estimation: In scenarios where overestimation can lead to issues, such as inventory or budgeting.
  • Simplifying Results: To create clear and concise data for reporting purposes.

Examples

Basic Usage

Round 123.456 down to 2 decimal places:


RoundedValue = ROUNDDOWN(123.456, 2)

Output: 123.46.

Column Usage

Apply rounding down to a column containing sales amounts:


RoundedSales = ROUNDDOWN(Sales[Amount], 0)

All sales amounts are rounded down to the nearest integer.

Advanced Usage

Combine with conditional logic to apply different rounding rules:


ConditionalRounding = IF(Sales[Amount] &gt; 1000, ROUNDDOWN(Sales[Amount], -1), ROUNDDOWN(Sales[Amount], 2))

Result: For sales over 1000, values are rounded down to the nearest tens; for others, they retain 2 decimal places.

Tips and Tricks

  • Use for Conservative Calculations: The ROUNDDOWN function ensures no overestimation, which is ideal for budgets or quotas.
  • Pair with Rounding Alternatives: Use alongside ROUND and ROUNDUP to implement complex rounding strategies.
  • Understand Negative num_digits: A negative value for num_digits truncates to powers of 10. For example:

    ROUNDDOWN(12345, -2) // Result: 12300

Performance Impact of ROUNDDOWN DAX Function:

  • Efficient for most calculations but can introduce rounding bias in large datasets.
  • Use sparingly in iterative calculations where rounding errors could compound.

Related Functions You Might Need

  • ROUND: Rounds a number to the nearest value based on standard rules.
  • ROUNDUP: Always rounds up to the nearest higher value.
  • TRUNC: Truncates numbers to a specified decimal place without rounding.

Want to Learn More?
For more information, check out the official Microsoft documentation for ROUNDDOWN. You can also experiment with this function in your Power BI reports to explore its capabilities.

If you’re looking to unlock the full potential of Power BI and take your data insights to the next level, our expert Power BI consulting services are here to help. Whether you need assistance with implementing advanced DAX functions like the ones discussed here, creating interactive dashboards, or optimizing your data models for better performance, our team of seasoned Power BI consultants is ready to provide tailored solutions for your business. Visit our Power BI Consulting page to learn more about how we can empower your organization with data-driven decisions.

1. What is the difference between ROUNDDOWN and TRUNC?

The ROUNDDOWN function always rounds towards zero, while TRUNC simply removes decimal places without rounding.

2. How does negative num_digits affect the result?

Negative num_digits truncates digits to the left of the decimal point. For example, ROUNDDOWN( 1234, −1 ) results in 1230.

3. Can ROUNDDOWN handle column values in Power BI?

Yes, you can apply ROUNDDOWN to calculated columns and measures for consistent rounding in data transformations.

4. What happens if num_digits is zero?

The function rounds down to the nearest integer.

5. When should I use ROUNDDOWN over ROUND?

Use ROUNDDOWN when you want consistent downward rounding without considering the next digit.

Previous article
Dax Function: ROUND
Next article
Dax Function: ROUNDUP