> ## Documentation Index > Fetch the complete documentation index at: https://domoinc-openapi-sync-dataflows.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Pivot Table export const InlineImage = ({src, alt = '', height = '1.6em'}) => { return {alt}; }; ### Intro In Domo, Pivot Tables are a powerful alternative to [HTML Tables](/s/article/360043429573 "Table Chart"). They allow you to more efficiently summarize large quantities of data from a DataSet and explore data by different dimensions and measures. HTML Tables, also known as Flat or Standard Tables, are useful for displaying information in a scannable format, but they are not always useful for summarizing and highlighting data. HTML Tables can be based on either columns or rows, but not both. This means you have to use a large amount of space to display information. Pivot Tables, on the other hand, allow you to display data using both rows and columns. Each cell in the table contains data for the intersecting column and row. You can also have multiple columns and/or rows. Each subsequent column or row you add becomes a new grouping within the previously added grouping. **Important:** Unless you set a row limit for your Pivot Chart manually, there is a default row limit of 25k. If you see the message **Warning: Not all data is shown** with rows in your Pivot Table displaying as **###**, numbers that may be in those places have not been included in the data used to create the Pivot Table because there are too many rows to process. In this case, there are some subtotals for the column, but because the number of rows of data is limited, the Pivot Table cannot be fully built. The **###** indicates that there is missing data to display. These cells may or may not have data, but they cannot be found due to the size of the data. **Important:** If you see the message— **Warning: Multiple results encountered for the same location** in your Pivot Table displaying as \*\*\*\*\*—check that the values are aggregated for your Beast Mode CASE statements. **Table Properties:** This article describes chart properties available only for Pivot Tables. A number of other available properties, including individual column formatting, are described in [Properties for Tables](/s/article/360042925374). *** ### Pivot Table Capabilities Pivot Tables provide the following capabilities: * [Alert integration](https://domo-support.domo.com/s/article/360043430373?language=en_US) * [Beast Mode support](https://domo-support.domo.com/s/article/360043429913?language=en_US) * Card-to-Card Filter support * [Color rules](https://domo-support.domo.com/s/article/360043428813?language=en_US) * Color/attribute control by row and column * [Connect and update data](https://domo-support.domo.com/s/article/360043429873?language=en_US), as with any other chart type * [Drill Path](https://domo-support.domo.com/s/article/360042924094?language=en_US) * [Domo Embed support](https://domo-support.domo.com/s/article/360043437973) * Expand/collapse rows and columns * Export to [Excel and CSV](https://domo-support.domo.com/s/article/360043437813?language=en_US) or [PowerPoint](https://domo-support.domo.com/s/article/360043437793?language=en_US) * Full integration with Analyzer * Full mobile support * Highlighting and selection by row and column grouping * Independent control over row and column totals * Much larger table handling * Quick Filters support * [Share via email](https://domo-support.domo.com/s/article/360043437873?language=en_US) * [Summary Numbers](https://domo-support.domo.com/s/article/360043428753?language=en_US) Pivot Tables have Table Calculation functionality. Follow this link to learn about [Table Calculations](https://domo-support.domo.com/s/article/360043429573#table_calculations). Pivot Tables also include several chart properties for configuring the look and feel of the chart. You can use chart properties to control the font size and color, text wrapping, header appearance, total and subtotal appearance, and more. For more information about changing chart properties, see [Customizing Pivot Tables](#CustomizingPivotTables), below. Just like in an HTML Table, you can include any DataSet columns you want in a Pivot Table and change the order of the columns as you like. For information about changing the order of columns, see [Sorting Rows and Columns](#SortingRowsAndColumns), below. ### Power Pivot Tables Unlike with other chart types—where you must drag columns of a specific data type into specific fields in the **Columns** region above the chart—you create Pivot Tables by dragging columns into any field you want: **Rows**, **Columns**, or **Values**. * Columns you drag into the **Rows** region display as groupings on the left side of the chart. * Columns you drag into the **Columns** region display as groupings at the top of the chart. * Columns you drag into the **Values** region are used to populate the inner data pane of the chart, with one value appearing for each row-column pair. If you drag more than one column into a field, each subsequent grouping becomes a subset of the previous grouping. You can change the order of a grouping by dragging a column to your desired location in the field. Columns retain their unique data types after you drag them into a field. You can select columns in the **Rows**, **Columns**, and **Values** fields to access additional options, such as aggregation and formatting. The specific available options depend on the data type. Columns in the **Dimensions** pane allow you to only set basic aggregation options, enter a table label, and change the styling. Columns in the **Measures** pane provide more aggregation options, a label setting option, and number formatting options. Learn more about the following topics: * See [Applying DataSet Columns to Your Chart](https://domo-support.domo.com/s/article/360043428713) for information about aggregation. * See [Formatting Values in Your Chart](/s/article/360042924134 "Formatting Values in Your Chart") for information about formatting numbers. * See [Understanding Chart Data](/s/article/360043428693 "Understanding Chart Data") for information about value, category, and series data. In Analyzer, you choose the columns containing the data for your table. For more information about choosing data columns, see our article about [Applying DataSet Columns to Your Chart](https://domo-support.domo.com/s/article/360043428713), and for information about formatting charts in Analyzer, see our [Analyzer Overview](https://domo-support.domo.com/s/article/360043428673) article. ### Customize Pivot Tables You can customize the appearance of a Pivot Table in several ways, many of which are possible with the **Chart Properties** tool. For more information about this tool, see our article about [Chart Properties](https://domo-support.domo.com/s/article/360042925374?language=en_US). # Pivot Table Properties #### General Properties * **Font Color** **—** Change the font color used for all text in the table. * **Allow Text to Wrap —** When enabled, text wraps in columns instead of disappearing off the right side of the column. * **Financial Style Negatives —** When this checkbox is selected, negative numbers are enclosed in parentheses; otherwise, a minus sign is used. * **Show Negative Numbers in Red** **—** When this checkbox is selected, negative numbers display in red. * **Apply Color Rules to Totals and Subtotals —** Select this option to apply your configured [color rules](/s/article/360043428813) to totals and subtotals in your table. * **Apply Color Rules to Headers —** Select this option to apply your configured [color rules](/s/article/360043428813) to headers in your table. **Tip:** If your color rules are not displaying as expected, go to **General** and make sure the **Apply Color Rules to Headers** box is checked. * **General Border Properties —** There are several general properties for border width and color available for Pivot Tables. Learn about these [border properties](https://domo-support.domo.com/s/article/360043429573?language=en_US#general_border_properties). Screenshot 2024-02-20 at 3.25.49 PM.png #### Header Row Properties * **Header Fill Color** — Select a background color for the header row of 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 select 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 Font Color** — Select the text color for the column names in your header row. This color overrides the table font color set in **General** > **Font Color**. Note that the names in the upper-left corner of your Pivot Table are considered both row and column names. If you specify both a **Header Row** font color and a **Header Column** font color, the **Header Row** font color takes precedence. For example, if you set the general font color to blue and the header row font color to red, the header row font color displays as red instead of blue. If you then select **Default**, the header row color reverts to blue. pivot_table_header_row_font_color.png * **Header Alignment** — Select the alignment (left, center, or right) for the column names in the header row. The default setting is **Left**. Note that the names in the upper-left corner of your Pivot Table are considered both row and column names. If you specify both a **Header Row** alignment and a **Header Column** alignment, the **Header Row** alignment takes precedence. In the example below, the column names are centered. pivot_table_header_row_alignment.png * **Header Font Style** — Select the font style (bold, italic, or bold-italic) for the column names in the header row. The default setting is **Bold**. Note that the names in the upper-left corner of your Pivot Table are considered both row and column names. If you specify both a **Header Row** font style and a **Header Column** font style, the **Header Row** font style takes precedence. In the example below, the font style is **Bold-Italic**. pivot_table_header_row_style.png * Header Row Border Properties — There are several header row properties for border width and color available for Pivot Tables. Learn about these [border properties](https://domo-support.domo.com/s/article/360043429573?language=en_US#header_row_border_properties). #### ​​​​​​Header Column Properties * **Header Fill Color** — Select a background color for the header column of 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 specify 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 Font Color** — Select the text color for the row names in your header column. This color overrides the table font color set in **General** > **Font Color**. Note that the names in the upper-left corner of your Pivot Table are considered both row and column names. If you specify both a **Header Row** font color and a **Header Column** font color, the **Header Row** font color takes precedence. For example, if you set the general font color to blue and the header row font color to red, the header row font color displays as red instead of blue. If you then select **Default**, the header row color reverts to blue. pivot_table_header_column_font_color.png * **Header Alignment** — Select the alignment (left, center, or right) for the column names in the header column. The default setting is **Left**. Note that the names in the upper-left corner of your Pivot Table are considered both row and column names. If you specify both a **Header Row** alignment and a **Header Column** alignment, the **Header Row** alignment takes precedence. In the example below, the row names are centered. pivot_table_header_column_alignment.png * **Header Font Style** — Select the font style (bold, italic, or bold-italic) for the row names in the header column. The default setting is **Bold**. Note that the names in the upper-left corner of your Pivot Table are considered both row and column names. If you specify both a **Header Row** font style and a **Header Column** font style, the **Header Row** font style takes precedence. In the example below, the font style is **Bold-Italic**. pivot_table_header_column_style.png #### Totals Properties * **Show Total Row —** Add a **Grand Total** row to the bottom of your table. This row sums all value columns and counts any string and date/time columns you added to the **Values** region. pivot_table_total_row.png * **Total Row Position** **—** Specify whether the total row displays after the data rows in your table (default) or before the rows, at the top. This property is available only when the **Totals** > **Show Totals Row** checkbox is selected. The example below shows a table with the total row placed before the data rows. pivot_table_total_row_position.png * **Total Row Fill Color** **—** Select a background color for the total row in your table. If you select **Default**, no color is used. This property is available only when the **Totals** > **Show Total Row** checkbox is selected. pivot_table_total_row_background_color.png * **Total Row Font Color —** Select the text color for the content in your total row. This color overrides the table font color set in **General** > **Font Color**, and this property is available only when the **Totals** > **Show Total Row** checkbox is selected. For example, if you set the general font color to blue and the total row font color to red, the total row font color displays as red instead of blue. If you then select **Default**, the total row color reverts to blue. pivot_table_total_row_font_color.png * **Show Total Column** **—** On the right side of your table, add separate totals columns for each column you have dragged into the **Values** region above the chart preview. Each column sums all value columns and counts any string and date/time columns you added to the **Values** region. In the example below, a total column displays for both "Profit" and "Sales." pivot_table_total_column.png * **Total Column Fill Color —** Select a background color for the total column(s) in your table. If you select **Default**, no color is used. This property is available only when the **Totals** > **Show Total Column** checkbox is selected. pivot_table_total_column_background_color.png * **Total Column Font Color —** Select the text color for the content in your total column(s). This color overrides the table font color applied in **General** > **Font Color**, and this property is available only when the **Totals > Show Total Column** checkbox is selected. For example, if you set the general font color to blue and the total row font color to red, the total column font color displays as red instead of blue. If you then select **Default**, the total column color reverts to blue. pivot_table_total_column_font_color.png * **Total Column Font Style** **—** Select the font style (bold, italic, bold-italic, or underline) for the cells in the total column(s). The default setting is **Bold**, and this property is only available when the **Totals** > **Show Total Column** checkbox is selected. In the example below, the font style is **Bold-Italic**. pivot_table_total_column_font_style.png #### Subtotals Properties * **Show Subtotal Rows** **—** Add subtotal rows to your Pivot Table. Subtotal rows sum all value columns are summed and count any string and date/time columns you added to the **Values** field. One subtotal row displays for each of the primary **Rows** groupings in your table. In the example below, one such row displays for the "Furniture" grouping and another for the "Office Supplies" grouping. pivot_table_subtotal_rows.png * **Subtotal Row Position** **—** Specify whether subtotal rows display after the data rows in your table (default) or before the rows, at the top of each grouping. This property is available only when the **Subtotals** > **Show Subtotals Row** checkbox is selected. pivot_table_subtotal_rows_position.png * **Subtotal Row Fill Color —** Select a background color for the subtotal rows in your table. If you select **Default**, no color is used. This property is only available when the **Subtotals** > **Show Subtotal Rows** checkbox is selected. pivot_table_subtotal_rows_background_color.png * **Subtotal Row Font Color —** Select the text color for the content in your subtotal rows. This color overrides the table font color set in **General** > **Font Color**, and this property is only available when the **Subtotals** > **Show Subtotal Rows** checkbox is selected. For example, if you set the general font color to blue and the subtotal row font color to red, the subtotal row font color displays as red instead of blue. If you then select **Default**, the subtotal row color reverts to blue. pivot_table_subtotal_rows_font_color.png * **Subtotal Row Font Style —** Select the font style (bold, italic, bold-italic, or underline) for the text in the subtotal rows. The default setting is **Bold**, and this property is only available when the **Subtotals** > **Show Subtotal Rows** checkbox is selected. In the example below, the font style is **Bold-Italic**. pivot_table_subtotal_rows_font_style.png * **Show Subtotal Columns —** Add subtotal columns to your Pivot Table. Subtotal columns sum all value columns and count any string and date/time columns you added to the **Values** field. One subtotal column displays for each of the primary **Columns** groupings in your table. In the example below, one such column displays for the "Central" regional grouping. pivot_table_subtotal_columns.png * **Subtotal Column Fill Color** **—** Select a background color for the subtotal columns in your table. If you select **Default**, no color is used. This property is available only when the **Subtotals** > **Show Subtotal Columns** checkbox is selected. pivot_table_subtotal_columns_background_color.png * **Subtotal Column Font Color —** Select the text color for the content in your subtotal columns. This color overrides the table font color set in **General** > **Font Color**, and this property is available only when the **Subtotals** > **Show Subtotal Columns** checkbox is selected. For example, if you set the general font color to blue and the subtotal column font color to red, the subtotal column font color displays as red instead of blue. If you then select **Default**, the subtotal column color reverts to blue. pivot_table_subtotal_columns_font_color.png * **Subtotal Column Font Style —** Select the font style (bold, italic, bold-italic, or underline) for the text in the subtotal columns. The default setting is **Bold**, and this property is available only when the **Subtotals** > **Show Subtotal Columns** checkbox is selected. In the example below, the font style is **Bold-Italic**. pivot_table_subtotal_columns_font_style.png * **Suppress Single Item Subtotals —** Hide subtotals for all groupings that contain only one item. # Specify Table Labels To change the label that displays for any column or row in your table, follow these steps: 1. In the fields region above the chart, select the column for which you want to change the label. 2. Enter your desired label in the **Data Label** field. SpecifyTableLabels.png # Change the Aggregation Type for a Column All similar rows in a DataSet are automatically aggregated in a Pivot Table. The default aggregation is **Sum**, which sums the values for similar rows in a given column. You can change the aggregation type for a column to any of the following:

Aggregation Type

Description

Sum

Adds the values in similar rows.

Minimum

Displays the lowest value in similar rows.

Maximum

Displays the highest value in similar rows.

Average

Displays the average value of all similar rows.

Count

Displays the number of similar rows. For dimensional columns, this is the only available aggregation type.

To change the aggregation type for a column, follow these steps: 1. In the fields region above the chart, select the column for which you want to change the aggregation type. 2. Select your desired aggregation in the **Aggregation** field. ChangeAggregationType.png **Tip:** If you change the aggregation type for a column to something other than **Sum**, we recommend that you change the column label to reflect the change (as described in the previous steps). This is because most viewers automatically assume that similar rows in a DataSet are summed. For example, if you change the aggregation type for your "Sales" column to **Average**, you might want to change the label to something like "Average Sales." Otherwise, your viewers might mistakenly think that the values in the column have been summed. # Sort Rows and Columns In a Pivot Table, you can select the arrows to the left of any header row or column to change the sort pattern used. You can do this anywhere a card is displayed—in the Dashboard view, in the Details view, or in Analyzer. **Important:** Due to the back-end work that must be performed on your data in order for it to pivot, you MUST use the sorting arrows to sort the data, NOT the sorting field inside of Analyzer. indicates that these rows or columns are in the same order as the original DataSet. indicates that these rows or columns are in alphanumeric order. indicates that these rows or columns are in reverse alphanumeric order. **Important:** You may not be able to change the sort pattern when multiple row fields are present. In this case, you can resolve the problem by enabling the following properties: * Show subtotal rows * Suppress single item subtotals ### Best Practices for Pivot Tables To keep Pivot Tables organized and to avoid displaying unneeded data, the maximum number of queries that can be called is 36. To find this limit, use (NumberOfRowPills + 1) x (NumberOfColumnPills +1) ≤ 36. For example, a Pivot Table with 8-row pills and 1 column pill requires 18 queries to render: 8 rows x 1 column => (8+1) x (1+1) => 9 x 2 = 18 queries Possible row and column combinations include: 1 x 17 2 x 11 3 x 8 4 x 6 5 x 5 6 x 4 8 x 3 11 x 2 17 x 1 When you drag a new value to a column or row, you must place the new value in one of the **Drag Column Here** boxes. Existing values are replaced when you drag over them. The maximum number of columns allowed in a **Values** field is 50. # Use Case #1 The following image shows a portion of a typical Pivot Table. It 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 subcategories, and each region is broken down by state. For example, "Furniture" contains individual rows for "Bookcases" and "Office Furniture," and "Central" contains individual columns for "Illinois" and "Michigan." In addition, two columns of data are displayed: "Profit" and "Sales." These columns allow you to efficiently find the profit and sales values for the combination of any product and geographic location. For example, if you are asked to find the profit of office furnishings for the state of Michigan, simply 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 and individual columns for "Profit" total and "Sales" total on the right side of the table (not shown). pivot_table_example1.png # Use Case #2 In this example, a sales manager uses a Pivot Table to show the profits earned for all product categories. The sales manager wants to display the product categories in the rows, so they: 1. Drag the "Product Category" column from the ****Dimensions**** pane into **Rows** above the chart preview. 2. Drag the "Profit" column from the **Measures** pane into **Values** above the chart preview. This generates a basic Pivot Table that displays the profits for the three categories and their grand total, as shown below. 1.png At this point, the table does not provide much useful information, so the sales manager decides to expand the table to include data for regions. They drag the "Region" column into **Columns** above the chart preview. This adds a "Region" header row at the top of the table, adds a "Profit " column to the right side of the table, and divides the table into four "Profit" columns, one for each region. 2.png This is more helpful, but still lacks specifics. The table says that all the furniture generated a total profit of \$152,374.80, but we don't know the breakdown of the furniture types and how they contribute to this total. The sales manager realizes this, so they decide to add another column, "Product Sub-Category," to the table. They do this by dragging the "Product Sub-Category" column into **Rows** above the chart preview, just as they did with "Product Category." This divides each product category into subcategories, and each subcategory now displays individual profit values. 3.png **Note:** You do not need to include columns or rows in their logical breakdown order like in the previous example. If the sales manager switches the order of "Product Category" and "Product Sub-Category," the data will still display correctly. However, instead of the subcategories displaying in their own easy-to-read groupings, as shown above, each will be listed in alphabetical order and paired with its parent category, as shown below. Because this is harder to read, we recommend that you always arrange the columns in a field in their logical breakdown order. 4.png The sales manager completes their Pivot Table by adding two more columns. They drag "Customer State" into **Columns** and "Sales" into **Values**. This divides the regions into individual states (similar to what the sales manager did with "Product Category" and "Product Sub-Category") and adds a new data column, "Sales," for each state. 5.png At this point, the sales manager can make various adjustments to the Pivot Table to make it more readable and useful to viewers. They could set color rules to draw attention to values in a certain range, change the sort and aggregation type for certain columns, highlight total and subtotal rows and columns, add a Drill Path, and much more. For more information about these customizations, see the section about [Customizing Pivot Tables](#CustomizingPivotTables) above.