Introduction to CrossTab in Tableau
CrossTab as the name suggests refers to Cross Tabulation. It is one of the ways of visualizing the data by showing the relationship between two or more variables. In context to Tableau, Crosstab is also popularly known as “TEXT TABLE” or “PIVOT TABLE”.
It is one of the most basic forms of visualization formed in tableau using one or more measures and dimensions. The data presented on the cross tab can also be represented in the form of a calculated field e.g. Running total, percentage, etc.
Before making our crosstab for the day let us familiarize ourselves with some of the most frequently used terminologies.
Tableau by default divides the entire data into two categories
- Dimension: Contains qualitative values Such as Name, Date, Location, etc. They affect the level of detail of your data.
- Measures: Contains the quantitative values / Numeric values. Calculations can be performed on measures.
Image – 1 The part which is highlighted in yellow shows the Dimensions and measures.
Problem Statement
Suppose you are the manager of the Restaurant and wanted to prepare for a meeting with the stakeholders who are interested to know about the Restaurant’s performance over the period of time. As a manager, you can display the sales of the products over a period of time or the Profit of the restaurant over the period of time.
Let us progress with building a strong insightful report using Tableau’s Crosstab.
Forming a CrossTab in Tableau
We have the data available with us about the Restaurant. We are going to use the Excel file as our data source. Please find the file attached.
Let us do it together to have a better understanding of the crosstabs/text tables.
Step 1: Connect to the “Microsoft Excel” file.
Image – 2 showing the data source to be connected.
Step 2: Select the file from the location where the file exists In this case, we have to select the file ‘Coffee File.xlsx’. The below screen appears once the data is retrieved from the excel sheet.
4.5 (2,770 ratings)
View Course
Image – 3 shows Coffee File Data.
Step 3: Open a new worksheet. The top right corner displays the variety of charts available in Tableau. One of them is the Text Table also knows as CrossTab. As highlighted in the box it requires one or more dimensions and one or more measures for forming a text table.
Image – 4 shows along with the required Dimension & Measures for the particular chart type.
Step 4: Drag and drop the dimensions “Product Type” and “Products” in the row shelf.
Step 5: Drag and drop the dimensions “Date” in the column shelf.
Step 6: Drag and drop the measures “Sales” under the Marks Shelf.
The outcome of the above action forms a “Crosstab”.
Image – 5 shows the outcome after steps 4,5 and 6.
Step 7: Color-Coded Text Table
Make the chart even more attractive and insightful by adding colors to the measures. Just drag and drop the Measure “Sales” in the color Mark card and your values will be color-coded.
Image – 6 shows the Color Coded Crosstab
The darker shade represents a higher sale while a lighter shade represents a lower sale.
Now your chart has started narrating its own story and this is what you might have aimed for.
Step 8: Row Calculations – As already mentioned, we can also perform calculations on the measures. Follow the below steps.
- Click on the down arrow of the Sales field on the Mark card.
- Chose the Option Add Table Calculation.
- From the calculation type, select the “Percent of total” and selectTable(down) option from “compute using the field”.
- The Chart now shows the percentage of values.
Image – 8
Image – 9
Step 9: We can also form more reports using Profit as a measure and showcase in which the Product has the maximum profit over the given period of time.
Limitation of CrossTab in Tableau
1. Fixed Rows & Columns: By default, we have Six rows and columns in tableau. You can increase the number of rows and columns to a maximum of 16 by following the below-mentioned steps.
Image – 10 resize the number of Rows and Columns.
Image – 11 shows rows and columns size.
2. Difficult Interpretation: The Crosstab is the representation of data in a textual form which is comparatively little difficult to interpret than the other graphical forms of representation. You need to focus on reading and comparing the numbers for deriving any pattern or insights.
Where to Use the CrossTab in Tableau?
It is mostly preferred in the area where you want to display the relationship between two or more dimensions.
Good Practices while working with Tableau
- Naming convention: Give the valid name to everything from your Data source sheet or SQL query to your worksheet, Dashboard or Story. Following this practice from the very beginning will help you in the long run while building a hug dashboard having multiple sheets as well as data sources.
- Form Hierarchy: Spend the initial few minutes for just looking at the data and see if we can form any kind of hierarchies. In the case of Coffee File Data Product type and Product can form a Hierarchy. Also, Market Size, Market, state and Area Code can for another hierarchy
Conclusion – CrossTab in Tableau
Crosstab /Text Table because of its high resemblance with the Pivot table of excel is also known as Pivot tables. Crosstab being one of the most basic and popularly used forms of the chart in tableau. Adding the color and calculation can enhance the storytelling experience with your data.
Recommended Articles
This is a guide to CrossTab in Tableau. Here we discuss the problem statement, where to use and the formation of crosstab with its limitation. You may also look at the following articles to learn more-
