Skip to main content
waffle.svg
Domo Knowledge Base

Pivot Table - BETA

Version 5

 

Intro

Pivot tables provide a more powerful alternative to standard tables in Domo. They allow you to quickly and easily summarize large quantities of data from a DataSet as well as explore data by different dimensions and measures. 

Standard tables, also known as flat tables, are useful for displaying information in a scannable format but not so much for summarizing and highlighting data. These tables can be either column-based or row-based, not both, which means you end up using a great deal of space to display the information. With pivot tables, however, you can display data using both rows and columns. Each cell in the table contains data for the intersecting column and row. You can even have multiple columns and/or rows. Each subsequent column or row you add appears as a new grouping within the previously added grouping. 

The following screenshot shows a portion of a typical pivot table. This example contains two row groupings, "Product Category" and "Product Sub-Category," and two column groupings, "Region" and "Customer State." Each product category is broken down into its constituent subcategories, and each region is broken down by state. So "Furniture" contains individual rows for "Bookcases," "Office Furnishings," and so on. "Central" contains individual columns for "Illinois," "Michigan," etc. In addition, two columns of data are displayed—"Profit" and "Sales." Thus, you can easily pinpoint the profit and sales values for the pairing of any given product and geographic location. For example, if you were asked to provide the profit amount for office furnishings for the state of Michigan, all you would need to do is find the cell at the intersection of "Office Furnishings" and "Michigan"/"Profit," which is $4,784.35. You can also find the totals for each column at the bottom of the table, as well as individual columns for "Profit" total and "Sales" total on the right side of the table (not shown). 
  

pivot_table_example1.png        

Pivot table functionality has long been available in Domo in the form of Sumo cards. However, Sumo cards, as their own card type, lack many of the features available in KPI cards. Now that pivot tables are available as part of Domo's repertoire of charts, they provide all of the following previously unavailable capabilities:

Pivot tables also include various Chart Properties for configuring the look and feel of the chart. You can use these to control the font size and color, text wrapping, header appearance, total and subtotal look and feel, and so on. More information about Chart Properties is available later in this article.  

As with a standard table, in a pivot table you can include whichever DataSet columns you want. You can also change the order of the columns as desired.

Powering Pivot Tables

Unlike other chart types in which you must drag columns of a specific data type into specific fields in the Columns region above the chart, you build pivot tables by dragging columns into any of the fields you want—Rows, Columns, or Values. Columns you drag into the Rows area appear as groupings on the left side of the chart. Columns you drag into the Columns area appear as groupings above the chart. Finally, columns you drag into the Values area are used to populate the interior data area of the chart, with one value appearing for each row-column pairing. If you drag more than one column into a field, each subsequent grouping becomes a subgroup of the previous grouping. You can change the order of a grouping just by clicking and dragging a column to where you want it in the field. 

Columns still retain their unique data types after you have dropped them into a field. You can click columns in the RowsColumns, and Values fields to access additional options such as aggregation, formatting, etc. The specific options that appear depend on the data type. Columns from the Dimensions region on the left side of the screen only allow you to set basic aggregation options, enter a table label, and change the styling. Columns from the Measures region provide more aggregation options, a label setting option, and number formatting options. For more information about aggregating, see Aggregating your data. For more information about formatting numbers, see Formatting Values in Your Chart.     

For information about value, category, and series data, see Understanding Chart Data.

In the Analyzer, you choose the columns containing the data for your Table. For more information about choosing data columns, see Applying DataSet Columns to Your Chart.

For more information about formatting charts in the Analyzer, see KPI Card Building Part 2: The Analyzer.

Example

The following example shows how a sales manager, Eileen, might build a typical pivot table showing profits gained for all product categories. Eileen wants to show product categories in the rows, so she starts by 1) dragging the "Product Category" column name from the Dimensions listing into the Rows field above the chart preview area, and 2) dragging the "Profit" column name from the Measures listing into the Values field above the preview area. This generates a basic pivot table showing the profits for the three categories and a grand total, as shown here:
 

pivot_table_walkthru1.png
 

At this point the table does not provide much usable information, so Eileen decides to expand it by including data for regions as well. She drags the "Region" column name into the Columns field above the preview area. This adds a "Region" header row to the top of the table and splits the table into four "Profit" columns, one for each region. A "PROFIT TOTAL" column is also added to the right side of the table.
 

 pivot_table_walkthru2.png


This is slightly more helpful but still lacks specifics. We are told that furniture as a whole has yielded a profit of $152,374.80, but we don't know the breakdown of furniture types and how they are contributing to this overall value. Eileen realizes this. She decides to add another column, "Product Sub-Category," to the table. She does this by dragging the "Product Sub-Category" column name into the Rows field, just as she did with "Product Category." This breaks up each product category into its constituent subcategories, and individual profit values now appear for each subcategory. 
 

pivot_table_walkthru3.png  

Note that you do not have to include columns or rows in their logical breakdown order like in the previous example. If Eileen were to switch the order of "Product Category" and "Product Sub-Category," the data would still display correctly. However, instead of subcategories appearing in their own easy-to-scan groupings as shown above, they would be listed in alphabetical order and each would be paired with its parent category, as shown below. Since this is harder to read, it is recommended that you always order columns in a field in their logical breakdown order.   
 

pivot_table_walkthru4.png 

Eileen completes her pivot table by adding two more columns. She drops "Customer State" into the Columns field and Sales into the Values field. This has the effect of splitting the regions into individual states (similar to what she did with "Product Category" and "Product Sub-Category") as well as adding a new data column, "Sales," for each state.

pivot_table_walkthru5.png  

At this point Eileen could make various customizations to the pivot table to increase its readability and usefulness for viewers. She could set up color rules to draw attention to values in a certain range, change the sorting and aggregation type on given columns, highlight total and subtotal rows and columns, add drill path, and much more. Several of these customizations are described in the next section. 

Customizing Pivot Tables

You can customize the appearance of a Table in a number of ways.

Changing Chart Properties

Many customizations are possible by setting Chart Properties. For information about all chart properties, see Chart Properties.

Unique properties of pivot tables include the following. You can click a thumbnail image to see a larger image.

Property

Description

Example

General > Font Color

Lets you change the font color used for all text in the table.

General > Allow Text to Wrap

When enabled, text will wrap in columns instead of disappearing off the right side of the column.

General > Financial Style Negatives

When this box is checked, negative numbers are enclosed in parentheses; otherwise a minus sign is used.

General> Show Negative Numbers in Red

When this box is checked, negative numbers appear in red.

Header Row > Header Fill Color

Lets you select a background color for the header row in your table. If you select Default, no color is used.

Note that the names in the upper left corner of your pivot table are considered both row and column names. If you set both a Header Row fill color and a Header Column fill color, the Header Row fill color takes precedence. 

pivot_table_header_row_background_color.png

Header Row > Header Font Color

Lets you select the text color for the column names in your header row. This color takes precedence over the table font color applied in General > Font Color, so if you set the general font color to blue and the header row font color to red, the header row font color would appear red instead of blue. If you then chose Default, the header row color would revert to blue.

Note that the names in the upper left corner of your pivot table are considered both row and column names. If you set both a Header Row font color and a Header Column font color, the Header Row font color takes precedence. 

pivot_table_header_row_font_color.png

Header Row > Header Alignment

Lets you select the alignment for the column names in the header row. The default setting is Left.

In the example, the column names are centered.

Note that the names in the upper left corner of your pivot table are considered both row and column names. If you set both a Header Row alignment and a Header Column alignment, the Header Row alignment takes precedence. 

pivot_table_header_row_alignment.png

Header Row > Header Font Style

Lets you select the font style for the column names in the header row, either bold, italic, or bold-italic. The default setting is Bold.

In the example, Bold-Italic is set as the font style.

Note that the names in the upper left corner of your pivot table are considered both row and column names. If you set both a Header Row font style and a Header Column font style, the Header Row font style takes precedence. 

pivot_table_header_row_style.png

Header Column > Header Fill Color

Lets you select a background color for the header column in your table. If you select Default, no color is used.

Note that the names in the upper left corner of your pivot table are considered both row and column names. If you set both a Header Row fill color and a Header Column fill color, the Header Row fill color takes precedence. 

pivot_table_header_column_background_color.png

Header Column > Header Font Color

Lets you select the text color for the row names in your header column. This color takes precedence over the table font color applied in General > Font Color, so if you set the general font color to blue and the header row font color to red, the header row font color would appear red instead of blue. If you then chose Default, the header row color would revert to blue.

Note that the names in the upper left corner of your pivot table are considered both row and column names. If you set both a Header Row font color and a Header Column font color, the Header Row font color takes precedence.

pivot_table_header_column_font_color.png

Header Column > Header Alignment

Lets you select the alignment for the row names in the header column. The default setting is Left.

In the example, the row names are centered.

Note that the names in the upper left corner of your pivot table are considered both row and column names. If you set both a Header Row alignment and a Header Column alignment, the Header Row alignment takes precedence. 

pivot_table_header_column_alignment.png

Header Column > Header Font Style

Lets you select the font style for the row names in the header column, either bold, italic, or bold-italic. The default setting is Bold.

In the example, Bold-Italic is set as the font style.

Note that the names in the upper left corner of your pivot table are considered both row and column names. If you set both a Header Row font style and a Header Column font style, the Header Row font style takes precedence. 

pivot_table_header_column_style.png

Totals > Show Total Row

Lets you add a grand total row to the bottom of the table. In this row, all value columns are summed and all string and date/time columns you have added to the Values field are counted. 

pivot_table_total_row.png

Totals > Total Row Position

Determines whether the total row appears after the data rows in your table (default) or before the rows, at the top. This option is only available when the Totals > Show Total Row box is checked. The example shows a table with the total row before the data rows.  

pivot_table_total_row_position.png

Totals > Total Row Fill Color

Lets you select a background color for the total row in your table. If you select Default, no color is used. This option is only available when the Totals > Show Total Row box is checked.

pivot_table_total_row_background_color.png

Totals > Total Row Font Color

Lets you select the text color for the content in your total row. This color takes precedence over the table font color applied in General > Font Color, so if you set the general font color to blue and the total row font color to red, the total row font color would appear red instead of blue. If you then chose Default, the total row color would revert to blue. This option is only available when the Totals > Show Total Row box is checked.

pivot_table_total_row_font_color.png

Totals > Show Total Column

Lets you add total columns to the right side of the table. A separate total column is added for each column you have dropped into the Values field above the chart preview (thus, in the example, total columns appear for both "Profit" and "Sales"). For each column, all value columns are summed and all string and date/time columns you have added to the Values field are counted. 

pivot_table_total_column.png

Totals > Total Column Fill Color

Lets you select a background color for the total column(s) in your table. If you select Default, no color is used. This option is only available when the Totals > Show Total Column box is checked.

pivot_table_total_column_background_color.png

Totals > Total Column Font Color

Lets you select the text color for the content in your total column(s). This color takes precedence over the table font color applied in General > Font Color, so if you set the general font color to blue and the total row font color to red, the total column font color would appear red instead of blue. If you then chose Default, the total column color would revert to blue. This option is only available when the Totals > Show Total Column box is checked.

pivot_table_total_column_font_color.png

Totals > Total Column Font Style

Lets you select the font style for the cells in the total column(s), either bold, italic, bold-italic, or underline. The default setting is Bold. This option is only available when the Totals > Show Total Column box is checked.

In the example, Bold-Italic is set as the font style.

pivot_table_total_column_font_style.png

Subtotals > Show Subtotal Rows

Lets you add subtotal rows to your pivot table. In subtotal rows, all value columns are summed and all string and date/time columns you have added to the Values field are counted. One subtotal row appears for each of the primary Rows groupings in your table. In the example, one such row has been added for the "Furniture" groupings and another has been added for the "Office Supplies" grouping.

 

pivot_table_subtotal_rows.png

Subtotals > Subtotal Row Position

Determines whether subtotal rows appear after the data rows in your table (default) or before the rows, at the top of each grouping. The example shows a table with the total row before the data rows.   

This option is only available when the Subtotals > Show Subtotal Rows box is checked.

pivot_table_subtotal_rows_position.png

Subtotals > Subtotal Row Fill Color

Lets you select a background color for the subtotal rows in your table. If you select Default, no color is used.

This option is only available when the Subtotals  > Show Subtotal Rows box is checked.

pivot_table_subtotal_rows_background_color.png

Subtotals > Subtotal Row Font Color

Lets you select the text color for the content in your subtotal rows. This color takes precedence over the table font color applied in General > Font Color, so if you set the general font color to blue and the subtotal row font color to red, the subtotal row font color would appear red instead of blue. If you then chose Default, the subtotal row color would revert to blue. This option is only available when the Subtotals > Show Subtotal Rows box is checked.

pivot_table_subtotal_rows_font_color.png

Subtotals > Subtotal Row Font Style

Lets you select the font style for the text in the subtotal rows, either bold, italic, bold-italic, or underline. The default setting is Bold. This option is only available when the Subtotals > Show Subtotal Rows box is checked.

In the example, Bold-Italic is set as the font style.

pivot_table_subtotal_rows_font_style.png

Subtotals > Show Subtotal Columns

Lets you add subtotal columns to your pivot table. In subtotal columns, all value columns are summed and all string and date/time columns you have added to the Values field are counted. One subtotal column appears for each of the primary Columns groupings in your table. In the example, one such column appears for the "Central" regional grouping.

 

pivot_table_subtotal_columns.png

Subtotals > Subtotal Column Fill Color

Lets you select a background color for the subtotal columns in your table. If you select Default, no color is used.

This option is only available when the Subtotals  > Show Subtotal Columns box is checked.

pivot_table_subtotal_columns_background_color.png

Subtotals > Subtotal Column Font Color

Lets you select the text color for the content in your subtotal columns. This color takes precedence over the table font color applied in General > Font Color, so if you set the general font color to blue and the subtotal column font color to red, the subtotal column font color would appear red instead of blue. If you then chose Default, the subtotal column color would revert to blue. This option is only available when the Subtotals > Show Subtotal Columns box is checked.

pivot_table_subtotal_columns_font_color.png

Subtotals > Subtotal Column Font Style

Lets you select the font style for the text in the subtotal columns, either bold, italic, bold-italic, or underline. The default setting is Bold. This option is only available when the Subtotals > Show Subtotal Columns box is checked.

In the example, Bold-Italic is set as the font style.

pivot_table_subtotal_columns_font_style.png

Subtotals > Suppress Single Item Subtotals

Lets you hide subtotals for any groupings in which there is only one item in the grouping.

Specifying Table Labels

You can change the label that appears for any column or row in your table.

To change a table label,

  1. Click the column name you want to change in the fields region above the chart.

  2. Enter the desired label into the Label field.

Changing the Aggregation Type for a Column

All like rows in a DataSet are automatically aggregated in a pivot table. By default, Sum aggregation is used, meaning the values for like rows in any given column are summed. You can change the aggregation type for a column to any of the following:

Aggregation Type

Description

Sum

The values in like rows are added together.

Minimum

The lowest value in the like rows is displayed.

Maximum

The highest value in the like rows is displayed.

Average

The average value of all the like rows is displayed.

Count

The number of like rows is displayed. For dimensional columns, this is the only available aggregation type.

Median

The median value of all the like rows is displayed.

Std. Deviation

The standard deviation of all the like rows is displayed.

 To change the aggregation type for a column,

  1. Click the column name you want to change the aggregation type for in the fields region above the chart.

  2. Select the desired aggregation in the Calculation field.

Tip: When you change the aggregation type for a column to something other than Sum, it is recommended that you change the column label to reflect the change (as described in the previous steps). This is because most viewers automatically assume that like rows in the DataSet are being summed. For example, if you were to change the aggregation type for your "Sales" column to Average, you would probably want to change the label to something like "Average Sales." Otherwise your viewers may be misled into thinking the values in the column have been summed.  

Sorting Rows and Columns

You can click the arrows to the left of any header row or column in a pivot table to change the sort pattern used. You can do this anywhere the Card appears—in the Page view, Details view, or Analyzer.

pivot_sort_default.png indicates that these rows or columns are in the same order as the original DataSet

pivot_sort_alpha.png indicates that these rows or columns are in alpha-numeric order.

pivot_sort_reverse_alpha.png indicates that these rows or columns are in reverse alpha-numeric order.