Daily DAX : Day 191 MROUND

MROUND DAX Function

Syntax:

dax

MROUND(<number>, <multiple>)

    number: The value you want to round.

    multiple: The multiple to which you want to round the number.

Description:

The MROUND function rounds the number to the nearest value that is a multiple of the specified multiple. If the number is exactly halfway between two multiples, it rounds away from zero (e.g., 2.5 to 3 for a multiple of 1).

Return Value:

A number rounded to the nearest multiple of the specified value.

Examples

    Basic Rounding:

    dax

    MROUND(23, 5)

        Returns 25, as 25 is the nearest multiple of 5 to 23.

    Rounding to Nearest 10:

    dax

    MROUND(17, 10)

        Returns 20, as 20 is the nearest multiple of 10 to 17.

    Negative Numbers:

    dax

    MROUND(-23, 5)

        Returns -25, as -25 is the nearest multiple of 5.

    Decimal Multiples:

    dax

    MROUND(1.37, 0.5)

        Returns 1.5, as 1.5 is the nearest multiple of 0.5.

Use Cases

    Financial Reporting:

        Round revenue or expenses to the nearest 100 or 1000 for simplified dashboards.

        Example: MROUND([Revenue], 100) to round sales figures to the nearest 100.

    Inventory and Packaging:

        Round quantities to match standard pack sizes (e.g., boxes of 12).

        Example: MROUND([Units], 12) to round to the nearest dozen for inventory planning.

    Pricing Adjustments:

        Round prices to customer-friendly values, like the nearest $5 or $10.

        Example: MROUND([Price], 5) to set prices like $15, $20, etc.

    Time Intervals:

        Round durations or timestamps to the nearest 15-minute or 1-hour interval.

        Example: MROUND([Minutes], 15) for scheduling time slots.

    Data Simplification:

        Reduce precision in visuals for readability by rounding metrics to a specific multiple.

        Example: MROUND([WebsiteTraffic], 100) to show traffic in multiples of 100.

Practical Example in Power BI

Suppose you have a table Sales with a column [Revenue]. To create a new column that rounds [Revenue] to the nearest 50:

dax

RoundedRevenue = MROUND(Sales[Revenue], 50)

If [Revenue] is 123:

    MROUND(123, 50) returns 100, as 100 is the nearest multiple of 50.

Key Notes

    Error Handling: If multiple is 0, the function may return an error. Ensure multiple is non-zero.

    Precision: Works with both integer and decimal multiples, making it versatile for various scenarios.

    Performance: The function is optimized for DAX and performs well in most datasets.

    Comparison to Other Functions:

        Use CEILING or FLOOR for rounding up or down to a multiple.

        Use ROUND for rounding to a specific number of decimal places.

Example in a Measure

To calculate total rounded revenue in a measure:

dax

TotalRoundedRevenue = 

SUMX(Sales, MROUND(Sales[Revenue], 100))

This rounds each row’s revenue to the nearest 100 before summing.

The MROUND function in DAX is a powerful tool for aligning numerical data with business-specific multiples, enhancing reporting and analysis in Power BI.