2 Replies Latest reply on Aug 7, 2016 12:03 PM by Kurt Heisler

    Conditionally format values

    Kurt Heisler

      I have data like this:

       

      CategoryLabelDataFormatValue
      Value2
      ***FemaleNumber10001000
      ***MaleNumber8080
      ***TotalNumber10801080
      ***FemalePercent92.6.926
      ***MalePercent7.4.074
      ***TotalPercent1001

       

      ... and it continues with many other categories of data, some of which have only Numbers, only Percents, or both (like in the *** example).

       

      (I included both Value and Value2 in case one is easier to work with than another. I can create either in my raw data.)

       

      I would like to create a table that shows the numbers and percents side by side. For example:

       

      LabelNumberPercent
      Female1,00092.6%
      Male807.4%
      Total1,0800.0%

       

      The solution I've come up with is to use a calculated field, [Value2 Format % or N], in place of Value2.

       

      Value2 Format % or N:

       

      IF attr([Data Format]) <> "Number"

      THEN str(int(mid(str(round(min([Value2]),3)),3,2))) + "." + str(int(mid(str(round(min([Value2]),3)),5,1))) + "%" else str(round(min([Value2]),0))

      END

       

      This returns:

       

       

      Obvious problems:

       

      - The numbers don't have the comma separators

      - The Total is showing 0.0 instead of 100%

      - My calculated field is a string, which could cause problems down the road (these are numbers after all!)

       

      Any suggestions for handling this issue?

        • 1. Re: Conditionally format values
          Ashish Chaudhari

          Hi Kurt,

           

          I noticed that you are using bit older version of tableau (that has nothing to do with the problem that you are facing), thus you won't be able to open the workbook which I am sharing here.

          Kindly follow the below steps.

           

          Please refer to the screenshot below to prepare your output. Kindly exclude the Total from the Label dimension as you can calculate the total in this tool. (You can see I have excluded the totals.)

           

          Put actual values and not the formula that you have written. Put values again to the row section and right click, go to table calculations and select percentage of total and select direction as table down. Refer below screenshot for the same.

          For Totals go to analysis section, go to Totals and select "Show column Grand Total" option. Refer below image.

           

          This will take care of everything. Let me know if this helps.

           

          Thanks and Regards,

          Ashish Chaudhari

          • 2. Re: Conditionally format values
            Kurt Heisler

            Thank you! This does the trick.

            1 of 1 people found this helpful