Forum Discussion

SanderB's avatar
SanderB
Frequent Visitor
6 years ago

Round values in measure

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

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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.

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
      SanderB
      Frequent Visitor
      6 years ago

      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