Forum Discussion

Tejasvi_munge's avatar
Tejasvi_munge
Frequent Visitor
6 years ago
Solved

keep zero after decimal

Hi All,

 

I have a column containing values like 2.3456, 2.304 etc

And I want to limit values upto two decimal points. Also, I cannot use option which modelling ribbon provides beause I am connected live and that option is disabled and cannot use format function as it converts the column into text and will not be able to sort it in descending order.

Also, tried using round function. But, it does not provide expected result like-

If my values are like  2.3446, 2.304 etc then it should return 2.34, 2.30 (not 2.3)

 

Thank you in advance!

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    You can change measure's format:

    Or use VALUE function:

    measure 2 = VALUE([measure])

    Or if you just want to sort, please try to create a rank measure:

    rank = RANKX(ALLSELECTED('Table_1'),VALUE([Measure]),,DESC,Dense)

    And then sort number by this rank.

     

    Best Regards,

    Giotto Zhi

6 Replies

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

    Hi,

     

    Please try to use FIXED function to create a measure to show the data format you want, like this:

    Measure = FIXED(SELECTEDVALUE(Table_1[Data]),2)

    You can specific any number of decimal you want by this function, and it shows:

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

    • Tejasvi_munge's avatar
      Tejasvi_munge
      Frequent Visitor
      6 years ago

      Hi,

       

      Thank you for suggestion! 

      But Fixed changes the data type into text and will not give correct output while sorting 

      For ex- consider values like 9.8, 9.1,10.3

      Sorting(desc order) after using Fixed will give output like-9.8, 9.1, 10.3 and not 10.3, 9.8, 9.1   

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

        Hi,

         

        You can change measure's format:

        Or use VALUE function:

        measure 2 = VALUE([measure])

        Or if you just want to sort, please try to create a rank measure:

        rank = RANKX(ALLSELECTED('Table_1'),VALUE([Measure]),,DESC,Dense)

        And then sort number by this rank.

         

        Best Regards,

        Giotto Zhi

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    6 years ago

    Tejasvi_munge 

    I cannot check using a live connection (don't have such a model...) but can you check if you can format your measures in the modeling pane? Select a measure, expand the properties pane, choose "Formatting" and proceed as needed...

     

  • Razwan's avatar
    Razwan
    Helper I
    6 years ago

    Tejasvi_munge 
    Hi, since you are using a live connection, the only way to build a column is in its data source and not on the report-side. 
    However, you may try to build a measure on the report-side, something like:

    # MyMeasure = 
      CALCULATE(
          MAX('MyTable'[MyColumn])
      )

    Then you can format it using the Modeling ribbon as you wish and append it to your table in the report.


    Kind regards,
    Razwan