tableau exercises Building Text Tables

In Tableau, you typically create text tables (also called cross-tabs or pivot tables) by placing one dimension on the Rows shelf and another dimension on the Columns shelf. You then complete the view by dragging one or more measures to Text on the Marks card.

A text table uses the text mark type. Tableau uses this mark type automatically if the view is constructed using only dimensions (assuming the Mark type is set to Automatic).

To create a text table that shows sales totals by year and category, follow these steps:

1. Connect to the Sample - Superstore data source, which is included with Tableau Desktop.
2. Drag the Order Date dimension to Columns.
   The date is automatically aggregated by year, and column headers are created.
3. Drag the Sub-Category dimension to Rows.
   Row headers are created. Columns with headers plus rows with headers means that a valid table structure now exists. You must now add a measure to the view to see actual data (that is, numbers).
4. Drag the Sales measure to Text on the Marks card.
   The measure is automatically aggregated as a sum. The resulting view looks like this:
   
   Tableau automatically uses text as the mark type. Each cell in the table displays the sum of the sales for a particular year and sub-category.
   We can see that the Chairs and Phones sub-categories had the highest sales in every year.
5. Drag the Region dimension to Rows and drop it to the left of Sub-Category. Do this by positioning Region to the left of Sub-Category until you see a small triangle indicating that the new field will be inserted to the left of the existing field:
   
   The view updates to break out sales by region, in addition to year and sub-category:
   
   Regions are listed alphabetically. If you drop Regionto the right of Sub-Category, the view is organized first by sub-category, and then by region:
   
   You can use a table calculation to show percentages of total instead of raw dollar values. The key question with a table calculation is how to frame the calculation—the more dimensions in your view, the more complicated this question can become. In the current case there are three dimensions in the view—Order Date, Sub-Category, and Region. You could show percentages of total for a single dimension, but that can be unwieldy. For example, if you show percentages just by region, the percentages would be calculated across the two remaining dimensions: Sub-Category (there are 17 sub-categories) and Year(Order Date) (there are 4 years). So you would be dividing the total 17 x 4 = 68 ways. That would make for some tiny percentages. What might be useful is to show percentages using two dimensions: Year(Order Date) and Region. Then the percentages would be calculated on the remaining dimension: Sub-Category. To put that in visual terms, you would be calculating percent of total within each red box in the following image:
   
   The dimensions that you use to frame your calculation are called the addressing fields, and the fields (in this case a single field) in which you run your calculation are the partition fields. For more information on these concepts, see Addressing and Partitioning.
6. To create a table calculation to show percentages, right-click (control-click on Mac) the SUM(Sales) field on the Marks card, and then select Add Table Calculation:
   
7. In the Table Calculation dialog box, set Calculation Type to Percent of Total.
   The options in the dialog box change depending on the type of calculation you choose:
   
   For more information on the options in the Table Calculation dialog box, see Using Table Calculations in the Tableau Knowledge Base.
8. For the Calculation definition, select Pane (Down), and then click OK to close the Table Calculation dialog box.
   Now we see percentages calculated within each sub-category, duplicated for each year within each region. The numbers within each red box add up to 100%:
   
   Pane (Down) is the correct choice because it specifies that the calculation should be performed from top to bottom within each pane of the table. The table has two vertical dimensions, so Table (Down) would have calculated the percent of total from top to bottom for the entire table, ignoring the Region dimension. The pane is always the finest level of detail for the relevant direction (across or down). If you had three dimensions on the vertical axis, you might have had to use field names to define the calculation, since only the dimension furthest to the left on the Rows shelf (defined as Table) and the dimension furthest to the right (defined as Pane) could be captured with the structural options.
   These concepts can be daunting. One solution is to use trial-and-error to see the results of different definitions for table calculations. But you should have a clear idea of what result you want, so that you can recognize it when you see it.