5 Replies Latest reply on Sep 1, 2016 6:39 AM by Ruth Roulston

    Conditional formatting based on average of rows

    Ruth Roulston

      Our company has just started using tableau for ITIL metrics.  I have a list of 20000+ changes and have a table that has the month along the top and the closure code on the change down the left hand side.  Then a count of the number of changes in each month according to closure code.  For example:

       

      Closure CodeJanFebMarchAprilTOTAL
      Code 15677678908763100
      Code 24567654566532330
      Code 35636787642342,239

       

      Now that I have explained what I have, here is what I want to do:

      Colour coordinate either the cell OR the text depending on whether is the above or below the total average for that row

      For example:

      Code 2 in Jan is less than the average across the months so I would like it to appear in red

      Code 2 in Feb is above the average across that row so I want it to appear in green

        • 1. Re: Conditional formatting based on average of rows
          David Li

          Hi Ruth, you can do this using a table calculation. Try something like:

          SUM([Changes]) - WINDOW_AVG(SUM([Changes]))

          Of course, you'll have to change the measure name to the one in your data set. Put that into the Color mark and have it compute along the dates (or table across should work).

           

          This will give you a continuous color scale. If you want a binary color scale, change the minus sign to a > sign.

          • 2. Re: Conditional formatting based on average of rows
            Simon Runc

            hi Ruth,

             

            Started looking into this before David's response...so you may as well have the workbook!

             

            I've done it in a very similar way to David's suggestion...using WINDOW_AVG (which is set to compute-using = Table Across). I've done the WINDOW_AVG and then KPI colouring as 2 measures, so you can see what's going on (I've added WINDOW_AVG to the tooltip), but you can nest into a single calc in your final version.

             

            As David suggested, I've used a continuous measure (1 or 0) for my KPI, to get the highlight table affect. With the colouring set up as per the below, so we only get Red or Green.

             

             

            Hope that makes sense, but please post back if not.

            1 of 1 people found this helpful
            • 3. Re: Conditional formatting based on average of rows
              Ruth Roulston

              Perfect - thanks to both of you!  That worked :-)  Now I need to figure out a way to get the average to display on the table as well so that people understand why one is red and the other is green.

              • 4. Re: Conditional formatting based on average of rows
                Simon Runc

                So the easiest way, without making the table un-readable, is just to add this information to the tool-tip (you can edit these to display things how you want)...which I've done here

                 

                Alternatively...Tableau is a data-viz tool, so what about this

                 

                 

                This get's across the same information, is easy to see the difference in scales/movements (visually this is faster/easier for a human than holding/comparing values in memory)...and the Red/Green is self-explanatory...Just a thought!!!

                1 of 1 people found this helpful
                • 5. Re: Conditional formatting based on average of rows
                  Ruth Roulston

                  Thanks Simon :-) Appreciate all of those answers.  The last one has turned out to be more useful for other data I am displaying.  90% of the changes have the one closure code so displaying anything as line / scatter graphs leaves one line way up above the rest but using a bar chart works fine.

                   

                  Thanks to everyone who replied - it's been really useful!