KNOWLEDGE BASE

Add Additional Columns to A Crosstab when Every Row is a Measure


Published: 11 Oct 2016
Last Modified Date: 30 Jan 2018

Question

How to add additional columns to a crosstab containing multiple measures, aka where each row is a measure.

For example, the original crosstab may have two columns for the last two years, and the goal is to add a difference column.

Environment

Tableau Desktop

Answer

The size and structure of the data set, whether or not all the data displayed has the same number formatting, and the particular view being built will all affect the exact solution needed. All of the below options are demonstrated in the attached workbook. Please be aware, that each solution has its own limitations.
 
CLICK TO EXPAND SOLUTION
OPTION 1: Add and customize a grand totals column
 
Note: It is only possible to add one extra column to a view with this method, and every measure in the view must be replaced with a calculated field.
  1. Navigate to Analysis > Totals > check Show Row Grand Total
  2. Create a calculated field with a name like "Sales or Difference" with a calculation similar to the following:
    IF SIZE() > 1
    THEN SUM([Sales])
    ELSE
        SUM(
            IF YEAR([Order Date]) = 2015
            THEN [Sales]
            END
        )
        -
        SUM(
            IF YEAR([Order Date]) = 2014
            THEN [Sales]
            END
        )
    END
  3. Replace [Sales] with [Sales or Difference] on the Measure Values card
  4. Repeat steps 2-3 for every measure in the view
  5. OPTIONAL: FORMAT THE GRAND TOTAL COLUMN
    1. Right-click "Grand Total" in the view and select Format…
    2. Navigate to Format > Font…
    3. In the left-hand Format Font pane, for Grand Total in the Pane dropdown select Tableau Book
    4. At the top of the Format Font pane, in the Fields dropdown, select YEAR(Order Date)
    5. For Grand Total enter the desired column name in the Label box. In this example, I renamed "Grand Total" to "Difference"
    6. Click the Border icon, which looks like a square grid
    7. For Total, for both Pane and Header select None
    8. Click the x in the top right-hand corner of the Format pane to close it
 
CLICK TO EXPAND SOLUTION
OPTION 2: Overwrite unused dates to create extra columns
 
Note: All numbers in the same row must have the same number format, and every measure in the view must be replaced with a calculated field.
 
  1. Select Analysis > Create Calculated Field
  2. 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 "Column Headers"
    • In the formula field, create a calculation similar to the following:
       
      If YEAR([Order Date]) <= 2015
      AND YEAR([Order Date]) >= 2014
      THEN STR(YEAR([Order Date]))
      ELSEIF YEAR([Order Date]) = 2013
      THEN "Difference"
      ELSE "% Difference"
      END
  3. Replace YEAR(Order Date) on the Columns shelf with [Column Headers]
  4. Remove [Order Date] from the Filters shelf
  5. Create a calculated field with a name like "New Sales" with a calculation similar to the following:
     
    If YEAR(MIN([Order Date])) <= 2015
    AND YEAR(MIN([Order Date])) >= 2014
    THEN SUM([Sales])
    ELSEIF YEAR(MIN([Order Date])) = 2013
    THEN LOOKUP(ZN(SUM([Sales])), -1) - LOOKUP(ZN(SUM([Sales])), -2)
    ELSE (LOOKUP(ZN(SUM([Sales])), -1) - LOOKUP(ZN(SUM([Sales])), -2)) / ABS(LOOKUP(ZN(SUM([Sales])), -2))
    END
  6. Replace [Sales] with [New Sales] on the Measure Values card
  7. Repeat steps 4-5 for all measures to be included in the view
  8. Right-click "Column Headers" in the view and select Hide Field Labels
CLICK TO EXPAND SOLUTION
OPTION 3: Pivot Measures
Note: All number values in the same column must have the same number formatting. Pivoting measures may cause issues creating additional views. If using this method, consider making a data source just for this view.

PIVOT ALL OF THE DESIRED MEASURES
 
For some data sources, it is possible to pivot the data in Tableau Desktop. For directions, please see Quick Start: Pivot Data from Columns to Rows. For all other data sources, it will be necessary to pivot the data in the data source itself.
 
CREATE A CALCULATED FIELD FOR EACH COLUMN IN THE FINAL VIEW
 
  1. Select Analysis > Create Calculated Field
  2. 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 "2014 Values"
    • In the formula field, create a calculation similar to the following:

      IF YEAR([Order Date]) = 2014

      THEN [Pivot Field Values]

      END

  3. Create a calculated field with a name like "2015 Values" with a calculation similar to the following:

    IF YEAR([Order Date]) = 2015 THEN [Pivot Field Values] END

  4. Create a calculated field with a name like "Difference" with a calculation similar to the following:

    SUM([2015 Values ]) - SUM([2014 Values])

  5. Create a calculated field with a name like "% Difference" with a calculation similar to the following:

    ( SUM([2015 Values ]) - SUM([2014 Values]) ) / SUM([2015 Values ])


BUILD THE VIEW
  1. Drag [Pivot Field Names] onto the Rows shelf.

  2. Drag [2014 Values] to Text on the Marks card.

  3. Double-click each calculated field to add them to the view.

  4. Right-click "Pivot Field Names" in the view and select Hide Field Labels for Rows.

 
 
CLICK TO EXPAND SOLUTION
OPTION 4: Create separate worksheets for each column and combine them on a dashboard
 
Note: Worksheets will scroll independently on the dashboard if there are enough rows to need scroll bars. Also formatting on the dashboard can be tricky. However, this method allows for rows and columns to have different number formats, there is no limit on the number of additional columns, and minimal calculated fields are required.

FORMATTING TIPS:
  • Set dashboard to the resolution of the audience's screen

  • Hide titles for all but the first worksheet added to the view

  • Right-click [Measure Names] and uncheck Show Header

  • Add column titles with Text objects

  • Set all of the worksheets to fit width

  • Use blank objects to add space where needed

  • In Tableau Desktop 10.4+, set padding on the Layout pane to 0 for all worksheets

     

Additional Information

Tableau Desktop is designed to create views that take advantage of the data's underlying structure to help the audience understand data relationships at a glance. Therefore, another visualization may be better suited to present the data.

Consider some of the following suggestions to create views that help your viewers quickly find trends and answer questions:
  • Add all measures to the Measure Values card and use color to group measures.
  • Add information that would be in additional columns to Tooltip
  • Create a highlight table


 
Did this article resolve the issue?