Using Crosstabs


1. Creating and Editing Crosstabs

>> Create a Crosstab (Data table)

  1. Drag [dimension] to [Columns] / [Rows]

  2. Drag [measures] to the [View]

    image-20210526140941075

    - Automatically brings in [Measure Names], [Measure Values] and [Measure values shelf ]


>> Edit the gridline

  1. right-click on a value in the crosstab

  2. select [Format]

    ​ image-20210526141958945



2. Working with Totals and Aggregation

>> Add totals and subtotals

  1. Form the Analysis Menu:

    [Menu] - [Analysis] -->

    [Totals] -->

    [Show Row Grand Totals] / [Show Column Grand Totals] / [Add All Subtotals]

    image-20210526143521096 image-20210526143456694

  2. From the Analytics Pane

    image-20210526143709607


>> Change the aggregation type of the view

When calculating [Average] for total, be careful with the difference between [weighted average] and [average]


  • For weighted average, change the aggregation on the measure field

    ​ image-20210526143836193


  • For average of the values showed in the view, change the aggregation from the Analysis Menu.

    ​ image-20210526145935997



3. Creating Highlight Tables

>> Why building highlight tables?

Crosstabs is difficult to find outliers or make comparisons across categories.

However, with highlight we can easily emphasize outliers and trends.


>> How to build a highlight table?

  • Method 1: Normal Way (use Marks)

    1. A highlight table encodes the measure on Text and Color

      • Drag the [measure] field to [Text] Mark
      • Hold [Ctrl] and drag the [measure] [Text] Mark to the [Color] Mark

    2. Set the mark type to [Square]

    3. Set the [Columns] dimension and [Rows] dimension

      image-20210526170544332

    4. Edit colors to highlight outliers

      • If you want to distinguish data that below or above a particular value by color:

        choose a color palette with white in the center first

        then set the particular value as the midpoint ( [Center] ) of the color gradient

      image-20210526170411482 image-20210527092546019

  • Method 2: use [Show Me]

    1. select the measures and dimensions you’re interested in

    2. click the [highlight table] icon in the [Show Me] menu

      ​ image-20210526170921504



4. Creating Heat Maps

  • Create a heat map:

    • Need 1 or more [dimensions], and 1 or 2 [measures]
    • can create by using [Color] and [Size] marks or use the [Show me]

  • Adjust the view:

    • Edit Color:

      - For color-blindness, [Orange - (White) - Blue] palette is a good choice

    • Adjust Size:

      - Two small tick marks on the slider indicate the best range for the mark size

      image-20210528085803966