8 Replies Latest reply on Feb 22, 2018 1:22 PM by Rajesh Agarwal

    Custom Format Decimal based on data

    Heather Fingalson

      I'm looking to format a number field based on the size of the data.

      For larger numbers (over 10) I don't want to show a decimal place. for numbers less than 10 and greater than 1 I want to show only one decimal place and for numbers less than one I want to show 2 decimal places.

       

      example

      150.92 would show 151

      5.92 would show 5.9

      0.92 would show 0.92

       

      in excel I would use a conditional format

      [<1]0.00; [<10]0.0, 0

       

      This doesn't work in tableau.

       

      I also tried a calculated field that rounded the values

       

      if sum([NUMBER] )<1 then round(sum([NUMBER] ),2)

      elseif sum([NUMBER] )<10 then round(sum([NUMBER] ),1)

      else round(sum([NUMBER] ),0)

      end

       

      then entered a custom format of  #,##0.##

      It almost worked,

      but for whole numbers, it's showing the decimal. example 150.92 shows as  151.

       

      Any Ideas how to get it to work?