3 Replies Latest reply on Feb 12, 2019 9:11 AM by Matt Lutton

    Cell Color-Coding

    Dean Frickey
    Dean Frickey Feb 11, 2019 1:40 PM

      I have some data that I am presenting in tabular form in Tableau.  I would like to be able to color-code specific cells based on some defined thresholds. 

       

      I’ve done some searching and found information on “Legends per Measure” (https://onlinehelp.tableau.com/current/pro/desktop/en-us/whatsnew_desktop.htm#legends_per_measure) but that doesn’t do what I need because it gives a background color to the entire row.  I also looked at “Conditionally Formatting Cell Background Color” (https://kb.tableau.com/articles/howto/conditional-format-row-background-color) but it, too, didn’t seem to be what I needed because I’m using Measured Names.  So, I have decided to post this question to the forum.

       

      I have attached sample2.twbx which produces the viz shown here.

       

      actual viz.png

       

       

       

       

      This is what I would like Tableau to produce.

      desired viz.png

       

       

       

      Where the color-coding thresholds are defined by the following.

       

      > 0.5% = yellow

      > 2.0% = orange

      > 4.0% = red

       

      Also, notice that I’m only interested in the color-coding in the “NO” columns and the “% Count” and “% Volume” rows.

       

      Any ideas to how I can do this would be appreciated.

       

      Thank you.

       

      - Dean

       

      Message was edited by: Dean Frickey I'm updating the Tableau package.

        • 1. Re: Cell Color-Coding
          Michel Caissie
          Michel Caissie Feb 11, 2019 11:31 AM (in response to Dean Frickey)

          Dean,

           

          Here is on possible solution.

          Create two color logic calculation for each measure you want to color.

           

          case ATTR( [Valid] )

          when 'NO' then

              if SUM([% Count]) > .04 then 'CntRed'

              elseif SUM([% Count]) > .02 then 'CntOrange'

              elseif SUM([% Count]) > .005 then 'CntYellow'

              else 'CntWhite'

              end

          else 'CntWhite'

          end

          and

          case ATTR( [Valid] )

          when 'NO' then

              if SUM([% Volume]) > .04 then ' VolRed'

              elseif SUM([% Volume]) > .02 then ' VolOrange'

              elseif SUM([% Volume]) > .005 then ' VolYellow'

              else 'VolWhite'

              end

          else 'VolWhite'

          end

           

          Concatenate both in what will be your final Color measure

          [Color(%Count)] + [Color(%Volume)]

           

          Now, Ctrl-Click  Color and Measure Names  and drag both on the color shelf.

          You can see that you get a color for every possible combinations. Just set the colors accordingly.

          • 2. Re: Cell Color-Coding
            Dean Frickey
            Dean Frickey Feb 11, 2019 1:39 PM (in response to Michel Caissie)

            Thank you.  I'll try using your idea.

            • 3. Re: Cell Color-Coding
              Matt Lutton
              Matt Lutton Employee Feb 12, 2019 9:11 AM (in response to Dean Frickey)

              If you want to dive in and learn more about why this is challenging in Tableau versus Excel, I would recommend the following blog/reading:

               

              Older But Still Useful – Conditional Formatting | Drawing with Numbers

               

              Anything is possible in Tableau, but Tableau works with 'marks' and not 'cells' -- the trick in the above example is using multiple dual axes setups to produce multiple marks cards. So, finding a way to color the "marks" in your view should always be the goal in Tableau, and with text, that means all the text on a marks card gets whatever is on the color shelf.

               

              I hope this helps -- sometimes just changing the chart into a multiple measure bar chart or something else visually helps to make the conditional formatting more consumable in Tableau.

              1 of 1 people found this helpful