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.
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
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.
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.
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.
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.