DAX Query - Rounding

[jcarville]

Hi,

I have the following query to give me a recommended selling price for an item:

Recommended Selling Price =

IF(HASONEVALUE(SAGE_Extract[Symbol]),

            [Cost Price]

        + [Cost to Deliver]

        + [Haulage]

         - [Discount - Volume (£&€)])

I want to apply my own rounding rules so that if a selling price ends with a certain number a rule is applied e.g. ends with a 4 then round to 5, ends with a 6 then round to 5, ends with an 8 then round to 8.

I have tried using FORMAT to change the number to text, REPLACE and RIGHT to get the last character and a SWITCH function wrapped around this to give the different rounding options, but cannot get the DAX correct. Can anyone help?

Thanks.

[v-jiascu-msft]

Hi @jcarville,

Maybe the function MROUND could help.

Column = MROUND([Column1],0.3)

Best Regards,

Dale

[jcarville]

Hi @v-jiascu-msft,

MROUND does not meet my requirements as can be seen below by the inconsistent values in my calculated column.

To be more specific, these are the roundings I need to apply:

[v-jiascu-msft · Accepted solution]

Hi @jcarville,

A IF statement would help. Please try the formula below.

Column =
VAR lastDigital =
    INT ( RIGHT ( FIXED ( [Column1], 2 ), 1 ) )
RETURN
    IF (
        lastDigital IN { 0, 2, 3, 5, 7, 8 },
        [Column1],
        MROUND ( [Column1], 0.05 )
    )

Maybe you need to pay attention to the numbers end with 9.

Best Regards,

Dale

[jcarville]

@v-jiascu-msft, amazing DAX knowledge! That works perfectly to suit my needs. A bit heavy on the processing side, but my model is large anyways.

Thanks for your help with this, much appreciated.

Jake