A crosstab chart 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 include 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: Product Revenue account and Cost of Sales account represent two top-level columns.
2 Nested Column: Time is grouped under each account – allows for a side-by-side comparison of Q4 2016 and Q1 2017 results. The time settings determine the period displayed. You can override the time selection from the Time toolbar at the bottom of the chart.
3 Row: Location dimension is the row – breaks down data by country sales regions.
An account, Time, or a dimension are grouped by the order that they appear in the Data Settings tab for both columns and rows.
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.
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.
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.
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
The data slices visualized on a single crosstab chart must be less than 5,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 sales results of two product for each quarter.
The Data Settings inset shows that Time comes before Accounts. You can reorder accounts within the column to change where they appear in the chart.
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 compares sales results by product for each quarter.
The Data Settings inset shows that Accounts come before Time. You can reorder accounts within the column to change where they appear in the chart.
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 ordered last.
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.
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.