Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Crosstab Charts

Introduces you to the crosstab chart in Discovery and explains how to group and slice data.

A crosstab visualizes data in tabular format. This chart is often used to compare relationships between data sets.

Structure of a Crosstab

The basic structure of a crosstab consists of columns and rows that includes at least one account and optional dimension. The intersection of a column and row represents a slice of data, such as revenue from the United States for Q4. 

The following is a crosstab chart that compares the prior quarter revenue results with the current quarter. Where:

1  Column: Prior quarter and current quarter represent two-top-level column. 

2  Nested Column: Product revenue and cost of sales are grouped under each quarter  – allowing for a side-by-side comparison of quarterly results. 

3  Row: Location dimension is the row – breaks down data by country sales regions. 

An account, time, or dimension data series are grouped by the order they appear in the Data Settings tab for columns and rows. Use the handler icon to reorder.

Slicing Data to Tell the Right Story

You have many configuration options for slicing your data in a crosstab chart. To better understand the options available, create a crosstab chart and experiment with grouping your accounts and dimensions.

  Rules
Account

Required. You must add at least one account. You can add the same account more than once to a chart.

Accounts groups can be a column or a row. By default, the Accounts group is a column. From the Data Settings data,  drag and drop the Accounts  group  from Columns section to the Rows section to  position accounts as rows.

You cannot delete the Accounts group.

Time

Optional. You can add Time only once to the chart as a column or as a row.

If you  omit Time in the chart, the time selections in the Time Settings tab determine the scope of data evaluated.

You can delete Time from the chart.

Dimension

Optional. You can add many unique dimensions or dimension members as a column or row.

You add a specific dimension or dimension member only once  to a chart.

You can delete dimensions from the chart.

Limitations and Design Considerations

Limitation: The data slices visualized on a single crosstab chart must be less than 1,000 cells. A data slice is the intersection of the accounts and dimensions in a crosstab chart. If you exceed this limit, the following message displays:

This Crosstab has exceeded the maximum number of allowable cells. This can occur when combining dimensions which have a large number of members.

Consider the following when designing a crosstab chart with multi-columns and multi-rows:

  • Data slices become more granular and targeted.
  • Layout can become more complex and potentially less readable.

Time - Accounts  Column Configuration

A Time - Account configuration is a common way to slice data in a crosstab chart. The following is an example of a Time - Account configuration that visualizes data for two quarters, where each quarter compares the sales results for two products. The Data Settings inset shows that time comes before accounts.

Example Column Order is Time - Accounts

Account - Time Column Configuration

An Account - Time configuration is another common way to visualize data in a crosstab chart. The following shows an Accounts - Time configuration that visualizes data for two products, where each product compares the sales results for two quarters. The data settings inset shows that the accounts come before time.

Crosstab Chart Comparing Quarter Sales for Desktop and Laptops

 

Dimension - Accounts - Time Column Configuration

You can get more granular data slices by adding more accounts and dimensions.

The following crosstab uses the Dimension - Accounts - Time configuration that compares sales results of each reseller. The following chart shows a Dimension - Accounts - Time configuration that compares sales results of each reseller by product and for each quarter. The data settings inset shows that the Level dimension comes before Accounts. Time is last.

Crosstab Chart that Compares Desktops and Laptops Sales by Resellers for Prior Year and Current for Desktops and Laptops

 

Account - Dimension Row Configuration

By default accounts are added to a crosstab chart as columns. If you prefer, you can switch accounts from columns to rows.

The following chart shows an Account - Dimension by Rows configuration that compares Profit & Loss by the Function dimension for each month in Q1.

  • Column: Time (3 months in a quarter)
  • Row: Accounts are first and ordered by Net Income, Operating Expenses, Assets, Liabilities and then the Function dimension.

Crosstab with Accounts as Rows Broken Down by Department and Months in Quarter as Columns

Multi Column - Multi Row Configuration

The following crosstab uses a multi-column, multi-row configuration. You are comparing expenses for months in the quarter by country and function. The Data Settings inset shows:

  • Columns: Account (Operating Expenses) is listed first and Time is second.
  • Rows: Dimension (Location) is listed first and Dimension (Function) is second.

Crosstab with a Multi-Column, Multi-row Configuration that shows an Expense Report Broken Down by Months in the Quarter and by Location and Department

  • Was this article helpful?