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

# Conditional formatting based on average of rows

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 Code Jan Feb March April TOTAL Code 1 567 767 890 876 3100 Code 2 456 765 456 653 2330 Code 3 563 678 764 234 2,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

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

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

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

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

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

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!