5 Replies Latest reply on Jul 15, 2016 7:58 AM by Joe Proulx

    Not getting the correct result in custom format $,%

    Anu Rekha

      Hi All

       

      Hope all are doing good.

       

      I am trying to a do a custom format since I want $ value and % value in single column.

       

      I am using 0.00%;$##,##0.00 to format.

       

      Except for one value $0.08 in 3rd line everything is coming correct.

      It should be -7.6%.

       

      I am attaching the workbook link below

       

      | Tableau Public .

       

      Any help is very much appreciated

       

      Thanks

      Anu

        • 1. Re: Not getting the correct result in custom format $,%
          Joe Proulx

          Hi Anu.

           

          Tableau uses the custom Excel format. By definition, this format works as follows (per this site):

           

          "It is very important to understand how cell formats are seen by Excel. Excel see a cells format as having four Sections. These are, from left to right Positive numbers, Negative Numbers, Zero Values and Text values. Each of these Sections are separated by a semi colon (;). If you create a custom number format you do not have to specify all four sections. By this I mean, if you included only two sections, the first section would be used for both positive numbers and zero values, while the second section would be used for negative numbers."

           

          When you use the format 0.00%;$##,##0.00, you are telling Tableau to mark positive numbers as percentages and negative numbers as dollars.

           

          If I reset the formatting on your M2 column, I see these raw values:

           

          forum.PNG

           

          Because the third value is negative, it invokes the '$##,##0.00' format and displays it as $0.08.

          • 2. Re: Not getting the correct result in custom format $,%
            Anu Rekha

            Thank Joe for reply,

             

            Is there any other Workaround?

            • 3. Re: Not getting the correct result in custom format $,%
              Joe Proulx

              Anu Rekha wrote:

               

              Thank Joe for reply,

               

              Is there any other Workaround?

              Can you help me understand what you are trying to do? What is the logic that should drive the differentiation between a percentage and a dollar amount?

              • 4. Re: Not getting the correct result in custom format $,%
                Anu Rekha

                Hi Joe

                 

                I will be getting the dollar amount and percentages in the same field.

                 

                Assume I will be getting a description in a separate column(saying Dollar or Percent) Des in our workbook.

                 

                So if the value is 'Dollar' or '$'(don't know this work, since its a symbol) so say 'Dollar' then the number should be formatted as Dollar amount i.e. $ 100.23 , otherwise if it 'Percent' it should be like 5.6%.

                 

                Please let me know if you need more info.

                 

                Once again thanks for looking in to this.

                • 5. Re: Not getting the correct result in custom format $,%
                  Joe Proulx

                  This is certainly where my expertise falls off...but I was thinking a calc something like:

                   

                  IF [Category]="Dollar" then "$"+STR([Amount]) ELSEIF [Category]="Percentage" THEN STR([Amount])+"%" END

                   

                  But when I mocked this up you do lose some control over how many decimal places are shown. Maybe someone has a better idea.