1 2 Previous Next 19 Replies Latest reply on Mar 7, 2018 10:55 AM by Christiaan Casilimas

# Color Coding Cells per Conditions

I need help figuring out how to color code certain cells per multiple conditions.

I've attached a sample workbook.

This would apply only to the "Pipe Created" measure. 1) I would like any cell with a value of "Stage 1" and is >=2000 to be colored green. If it's <= 1999, then leave the cell alone. 2) For a value of "Stage 2" and anything that is >= 1500, then make that cell green, otherwise leave it alone. 3) For a value of "Stage 3" and anything that is >= 1000, then make that cell green, otherwise leave it alone.

Any assistance is appreciated!

• ###### 1. Re: Color Coding Cells per Conditions

Hey Eric-

Give this a look and see if it works.

I used MIN(Number of Records) on Size, mark type bar with size dialed all the way up.

Color

IF ATTR([Stage]) = 1

THEN IF SUM([Pipe Created]) >= 2000

OR SUM([Pipe Open]) >= 2000

OR SUM([Pipe Won]) >= 2000

OR SUM([Pipe Lost]) >= 2000

THEN "Green" ELSE "White" END

ELSEIF ATTR([Stage]) = 2

THEN IF SUM([Pipe Created]) >= 1500

OR SUM([Pipe Open]) >= 1500

OR SUM([Pipe Won]) >= 1500

OR SUM([Pipe Lost]) >= 1500

THEN "Green" ELSE "White" END

ELSEIF ATTR([Stage]) = 3

THEN IF SUM([Pipe Created]) >= 1000

OR SUM([Pipe Open]) >= 1000

OR SUM([Pipe Won]) >= 1000

OR SUM([Pipe Lost]) >= 1000

THEN "Green" ELSE "White" END

ELSE "White"

END

9.2 workbook attached.

• ###### 2. Re: Color Coding Cells per Conditions

Thanks Adam. How do I get the conditional formatting to only apply to the Pipe Created column? I would like to leave the other columns alone.

How does the "Min ( Number of Records )" affect the coloring?

• ###### 3. Re: Color Coding Cells per Conditions

Oh whoops, you did say just pipe created.

If you just want the one column colored, you have to use a trick to turn your dimensions into measures and then label the mark with the actual value.

You have to remove measure names from the columns and filter.  Then you are going to put four copies of MIN(Number of Records) in the column shelf

Now you will see 5 marks cards.  All and one for each of the Min(Number of Records) in your column shelf.

What this is going to do is draw every mark on the number one.  This is what keeps the text all in straight column.  Then your actual Measure goes on label.  I am only using the color formula on the Pipe Created Marks Card.

The Other MIN(Number of Records) on size is what draws a bar behind the text of equal sizes throughout.  Dial size all the way up.

I simplified the color formula since you are only after pipe created.

Color

IF ATTR([Stage]) = 1 AND SUM([Pipe Created]) >= 2000

OR ATTR([Stage]) = 2 AND SUM([Pipe Created]) >= 1500

OR ATTR([Stage]) = 3 AND SUM([Pipe Created]) >= 1000

THEN "Green" END

The next step is to edit the axes for each MIN(Number of Records).  Edit the axis title to your measure name, fix the axis to start at 0 and end at 1, and remove all tick marks.

If you want to have the title at the top of the column, you need to add eight copies of MIN(Number of Records) to your column shelf.  On the second pill create a dual axis.  The 1st and 2nd marks cards should be identical.  You would edit the top axis like I just described and then blank out the title and tick marks on the lower axis.  Repeat this dual axis step on the 4th, 6th and 8th pills.

9.2 attached again.

3 of 3 people found this helpful
• ###### 4. Re: Color Coding Cells per Conditions

Thanks Adam, that was really complex! Is that the only way to do it? I'm thinking it might clutter my worksheet, especially if I need to add additional dimensions to it

Let's say I wanted to just highlight the number to green, instead of the cell. Is that process easier? How would I do that?

• ###### 5. Re: Color Coding Cells per Conditions

Hi Eric,

One of the approach that @adam suggested will work fine but will create complex view. instead you could achieve same by changing your data. i.e changing your measures(pipe open, close, won and lost) into dimension like (category).

Thanks, Nikunj

• ###### 6. Re: Color Coding Cells per Conditions

Hi Eric-

You can add additional dimensions to the row shelf without any complications in that solution.

The process is no different for coloring text vs. the cell behind.  The problem is that the color will applied to the entire dimension when a condition is met.  Because you have multiple measures in this view, you were using measure names as your column dimension.  We cannot reference Measure Names in a calculated field.  That is why when the condition was met that pipe created was over 2000 then it colored all cells for Willie across Measure Values.

As Nikunj suggested, you could reshape your data.  If your real data source is excel or text file, you can pivot directly in Tableau.

Pivot Data (from Columns to Rows)

However, your measures are calculated fields so this won't work unless you do those calculations in Excel and then pivot in Tableau.

I did that for you and attached the file below.  Now you can connect to this source and pivot these fields.

Now you have two columns for pivot field names and pivot fields values, you can rename these if you want.

Now we can reference the pivot field names in the calculation because it is a real dimension and can achieve what you want.

Color

IF ATTR([Pivot field names]) = "Pipe Created Excel"

THEN IF ATTR([Stage]) = 1 AND SUM([Pivot field values]) >= 2000

OR ATTR([Stage]) = 2 AND SUM([Pivot field values]) >= 1500

OR ATTR([Stage]) = 3 AND SUM([Pivot field values]) >= 1000

THEN "Green" END END

I made a copy of this and did it as just text too.

Excel file and 9.2 workbook attached.  Either way is a little bit of work.

• ###### 7. Re: Color Coding Cells per Conditions

Can either of you show me the answer where I can convert the measure to a dimension and show the highlighted cells? I would like the week ending and the category ( i.e. Pipe Created) at the top.

• ###### 8. Re: Color Coding Cells per Conditions

Hi Eric-

Follow the pivot example above.  Converts the measure to dimension and pipe created at the top.

• ###### 9. Re: Color Coding Cells per Conditions

My data is coming from SQL Server, so I can't do any calculations to the data that's feeding Tableau. Will I still be able to do the pivots example you provided? I noticed you added calculations to the Excel data file.

• ###### 10. Re: Color Coding Cells per Conditions

No. I mentioned that the pivot only works on Excel or text files and you can't pivot the calculated fields.  Your Pipe fields are calculated fields.

You would need to reshape the data on your SQL server if you wanted to achieve what you are trying to do.

Or you can use the first solution I gave you with the Multiple marks cards.

• ###### 11. Re: Color Coding Cells per Conditions

Ohh adam, if your data is coming from the sql server then it will be easy to build query or storedd procedure that generates appopriate data. i.e you could do pivoting in sql server itself. then you only need to pull that data to tableau. quite easy

• ###### 12. Re: Color Coding Cells per Conditions

If I added Subtotals to this , how could I prevent the subtotals from being highlighted to green? See Stage 3 Total, under Pipe Created = 1700

• ###### 13. Re: Color Coding Cells per Conditions

Hey Eric-

Modify the Color formula.  Looks like just one record per mark at this detail level.  So if the record is greater than 1 it is a total.

Color

IF SUM([Number of Records]) = 1

THEN IF ATTR([Stage]) = 1 AND SUM([Pipe Created]) >= 2000

OR ATTR([Stage]) = 2 AND SUM([Pipe Created]) >= 1500

OR ATTR([Stage]) = 3 AND SUM([Pipe Created]) >= 1000

THEN "Green" END END

9.2 workbook attached.

2 of 2 people found this helpful
• ###### 14. Re: Color Coding Cells per Conditions

That was a niceee trick. I mostly use sum(measure value) != total(sum(measure value). But that wouldn't work when you have only 1 mark and the sub total in that case would equal the mark itself. Great trick

1 2 Previous Next