KNOWLEDGE BASE

Conditionally Color the Text in a Crosstab


Published: 04 Apr 2014
Last Modified Date: 29 Nov 2017

Question

How to conditionally color the text in a crosstab, or text table, view.

Environment

Tableau Desktop

Answer

CLICK TO EXPAND SOLUTION
Option 1: Color each measure name based on its own value
  1. Upgrade to Tableau Desktop 10.2 or a newer version. For more information, see Upgrade Tableau Desktop
  2. Use the Legends per Measure feature.
CLICK TO EXPAND SOLUTION
Option 2: Color entire partitions differently based on dimension values
If the text values that need to be colored differently all belong to the same partition in the view, then the dimensions used to create that partition can be added to Color on the marks card. In the below example, we want consumer Sales from the east region to be blue.
  1. In Tableau Desktop, connect to Orders (Sample Superstore).
  2. Drag Segment to Columns.
  3. Drag Region and Order Date to Rows.
  4. Drag Sales to Text.
  5. Double-click Profit in the data pane.
  6. Drag Segment to Color.
  7. Drag Region to Detail
  8. Click the Detail icon next to Region and select Color.
  9. Repeat steps 7-8 for Measure Names
  10. Edit colors as desired.
    • To edit colors, open the color legend drop-down menu, and then select Edit Color. For more information, see Color in Tableau Help.
To view Option 2 in action, watch the video below.
Note: Video has no sound.
CLICK TO EXPAND SOLUTION
Option 3: Color the entire table based on the same conditions
For more complex coloring conditions, we can write a calculation to specify which values belong to which color group. Use this method if all of the conditions apply to all of the columns in the view. If each column or row should use a different set of conditions, then skip to Option 4.
  1. In Tableau Desktop, connect to Orders (Sample Superstore).
  2. Drag Segment to Columns.
  3. Drag Region and Order Date to Rows.
  4. Drag Sales to Text.
  5. Double-click Profit in the data pane.
  6. Select Analysis > Create Calculated Field
  7. In the Calculated Field dialog box that opens, do the following, and then click OK:
    • Name the calculated field. In this example, the calculated field is named "Negative Profit"
    • In the formula field, create a calculation similar to the following:

      IF SUM( [Profit] ) < 0
      THEN "red"
      ELSE "gray"
      END
  8. Drag Negative Profit onto Color.
  9. Edit the colors so that "red" is red and "gray" is gray.

    Note: Sales is also colored red when Profit is negative because the Negative Profit returns "red" for all records in the underlying data where profit is less than 0. These records will also have sales values.
To view Option 3 in action, see the video below.
Note: Video has no sound.
CLICK TO EXPAND SOLUTION
Option 4: Color each column based on a different condition

Step 1: Create calculated fields for each measure used

  1. For every measure that you want to include in the view, select Analysis > Create Calculated Field.
  2. Name the field. Consider including the name of the measure in it. For example, Sales Col.
  3. In the Formula field, type 0, and then click OK.

Step 2: Build the view

  1. Drag Region and Order Date to Rows.
  2. Drag Segment to Columns.
  3. Drag each calculated field that you have created to Columns.
  4. In the ALL Mark cards, ensure the Text is selected as a Mark Type.
  5. In each mark card, drag the associated measure to Text and to Color. For example, drag Sales to Text in the SUM(Sales Col) mark card. 
  6. Modify the color of each measure as desired.
  7. Right-click each measure on the axis and select Edit Axis
  8. Navigate to the Tick Marks tab > select None for Major tick marks and Minor tick marks, and then click OK.
  9. In the Rows, right-click any of the calculated field and select Format
  10. Click the Lines icon and navigate to the Columns tab. 
  11. In the Zero Lines drop-down menu, select None.
  12. In the Grid Lines drop-down menu, select None.

Step 3: (Optional): move titles to the top

  1. For each desired column in the finished crosstab, drag two copies of the calculated fields created in the directions above to Columns.
  2. Drag the desired measure to Text in both copies of the calculated field.
  3. Right-click on the second copy of the calculated field on the Columns shelf and select Dual Axis.
  4. Edit both the top and bottom axes as desired.
To view Option 4 in action, see the video below: 

 

Additional Information

Only Option 4 can color Dimensions in the view.

By design, Tableau Desktop does not apply color to dimensions. Restricting color to measures is one way to focus the viewer on the data in the view rather than on the headers. Therefore to color dimensions in a crosstab, we must place the dimension values in "container" calculated measures.
 
Did this article resolve the issue?