1 Reply Latest reply on Jul 28, 2016 3:03 PM by hannah.johnson

    Conditional Format based on increase/decrease from previous col, over 25 measures

    Scott Schneider



      I have a rather large report in Tableau.  This is linked via live connection to a Teradata data file.  It is set up to (eventually) be a rolling 12 month snap shot of various measures.  I have over 25 measures in the table with various number formats :dollar, number, integer, and percentages, as well as various calculations:Sum, AGG, Avg


      Due to the sensitivity of the data I can not attach a packaged workbook, so 'dummy' data is used for this question.


      This report view is exactly how I want, but I now want to make if more appealing (especially when I get 12 months of data).  To call out when measures are above or below its corresponding measure from the previous month.  Color code green when greater, red when lower, black when the same.


      Excel picture of what I want



      I was able to create a calculated field in an attempt to do this, but it is only linked to a single measure:



      I then applied this to the color icon and after playing with colors, got what I wanted, But calculation is only for the first row/measure, the rest of the rows follow the format of ACCT_CNT trends



      So is there a way to have each measure be independent of the others and have a row by row trend color format, based on change to previous col?

      I am not stuck to only having font color,  cell fill color, even shapes (up arrow, down arrow) would also work.


      I seen some suggestions that would require me to create 20+ FMT calculations, but I can not figure out how to apply more that one color format at a time

        • 1. Re: Conditional Format based on increase/decrease from previous col, over 25 measures

          The thing that makes this difficult is using Measure Names and Measure Values since Measure Values can not be used in a calculated field.


          There are a few options:


          -If you are using the same conditional formatting logic for every Measure

          1. Use Measure Values on Color. Double-click on the Measure Values color legend and change the colors to one of the "diverging" color gradients. Click on the stepped color option and use 3 steps. Click on Advanced and set the center value to 0.

          2. Pivot the data and use "pivot field values" in a KPI calculated field. Use "pivot field names" on Rows instead of Measure Names. Pivoting must be done before connecting to data in Tableau Desktop unless the data source is Excel or Text based (there is a pivoting function within Tableau Desktop for Excel and text files).


          -If you are not using the same conditional formatting logic for every Measure

          3. Use multiple, separate placeholders for each of those measures on Rows, instead of using only Measure Names on Rows. The following Tableau Public Viz is a great resource for learning how to conditionally format separate cells/columns:

          Crosstab of Many Colors

          Workbook: conditional formatting v4



          Hannah @tableau