Round values in measure

[SanderB]

Hello all,

I use a measure for the calculation of the average in a pivot, if the value does not start with < (because a < will give error, no AVG) but everything is rounded on 2 decimals, and I want to show more decimals.

There is a decimals column, with all the decimal places per component.

I use:

Measure = 
VAR _text = SELECTEDVALUE( RESULT[Merged] )
VAR _search = SEARCH( "<", _text, 1, 0 ) > 0
RETURN IF( _search, _text,  AVERAGE( RESULT[VALUE] ) )

And the result is:

This is what I want, if a result contains < or > it uses no calculation, just the first < or > value. If the results does not contain <  or > it calulates the average of 3 values. But every thing is rounded on 2 decimals.

This code does not have any influence:

Measure = 
VAR _text = SELECTEDVALUE( RESULT[Merged] )
VAR _search = SEARCH( "<", _text, 1, 0 ) > 0
RETURN IF( _search, _text,  ROUND(AVERAGE( RESULT[VALUE] ),5) )

and giving more decimal places in the pivot results in:

Is there a way to influence the decimals in a measure like this?

Thanks in advance!

Best regards,

Sander

[v-lid-msft]

Hi @SanderB ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[SanderB]

Hi @v-lid-msft ,

Very sorry for the late reply, I missed the reply 😞

You code looks very good, and in the example it does exactly what I want, but for some reason the FORMAT part mashed my table up.

The filters are not working anymore and no data is shown.

I will check my dataset and will let u know if it works.

Thank for the support!

Best regards,

Sander

[v-lid-msft]

Hi @SanderB ,

We can use the following formula to meet your requirement:

Measure =
VAR _text =
    SELECTEDVALUE ( RESULT[Merged] )
VAR _search =
    SEARCH ( "<", _text, 1, 0 ) > 0
RETURN
    IF (
        _search,
        _text,
        FORMAT ( ROUND ( AVERAGE ( RESULT[VALUE] ), 5 ), "0.00000" )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I don't know if I'm getting your problem fully but have you tried the simple way to go to the pane modelling and adjust the decimals there?

Best Regards,

Duarte Raminhos