# Using pivot tables Use pivot tables to show measure values for the intersection of two dimensions. Heat maps and pivot tables display data in a similar tabular fashion. Use a heat map if you want to identify trends and outliers, because the use of color makes these easier to spot. Use a pivot table if you want to analyze data on the visual. To create a pivot table, choose at least one field of any data type, and choose the pivot table icon. Amazon Quick creates the table and populates the cell values with the count of the column value for the intersecting row value. Typically, you choose a measure and two dimensions measurable by that measure. Pivot tables support scroll down and right. You can add up to 20 fields as rows and 20 fields as columns. Up to 500,000 records are supported. Using a pivot table, you can do the following: + Specify multiple measures to populate the cell values of the table, so that you can see a range of data + Cluster pivot table columns and rows to show values for subcategories grouped by related dimension + Sort values in pivot table rows or columns + Apply statistical functions + Add totals and subtotals to rows and columns + Use infinite scroll + Transpose fields used by rows and columns + Create custom total aggregations To easily transpose the fields used by the rows and columns of the pivot table, choose the orientation icon (![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/pivot-orientation.png)) near the top right of the visual. To see options for showing and hiding totals and subtotals, formatting the visual, or exporting data to a CSV file, choose the Menu items icon at top right. As with all visual types, you can add and remove fields. You can also change the field associated with a visual element, change field aggregation, and change date field granularity. In addition, you can focus on or exclude rows or columns. For more information about how to make these changes to a pivot table, see [Changing fields used by a visual in Amazon Quick](changing-visual-fields.md). For information on formatting pivot tables, see [Formatting in Amazon Quick](formatting-a-visual.md). For information on custom total aggregations for pivot tables, see [Custom total values](tables-pivot-tables-custom-totals.md). **Topics** + [ ## Pivot table features ](#pivot-table-features) + [ # Creating a pivot table ](create-pivot-table.md) + [ # Orienting pivot table values ](pivot-table-value-orientation.md) + [ # Expanding and collapsing pivot table clusters ](expanding-and-collapsing-clusters.md) + [ # Showing and hiding pivot table columns in Quick ](hiding-pivot-table-columns.md) + [ # Sorting pivot tables in Quick ](sorting-pivot-tables.md) + [ # Using table calculations in pivot tables ](working-with-calculations.md) + [ # Pivot table limitations ](pivot-table-limitations.md) + [ # Pivot table best practices ](pivot-table-best-practices.md) ## Pivot table features Pivot tables don't display a legend. To understand the features supported by pivot tables, use the following table. | Feature | Supported? | Comments | For more information | | --- | --- | --- | --- | | Changing the legend display | No | | [Legends on visual types in Quick](customizing-visual-legend.md) | | Changing the title display | Yes | | [Titles and subtitles on visual types in Quick](customizing-a-visual-title.md) | | Changing the axis range | Not applicable | | [Range and scale on visual types in Quick](changing-visual-scale-axis-range.md) | | Changing the visual colors | No | | [Colors in visual types in Quick](changing-visual-colors.md) | | Focusing on or excluding elements | Yes, with exceptions | You can focus on or exclude any column or row, except when you are using a date field as one of the dimensions. In that case, you can only focus on the column or row that uses the date dimension, not exclude it. | [Focusing on visual elements](focusing-on-visual-elements.md) [Excluding visual elements](excluding-visual-elements.md) | | Sorting | Yes | You can sort fields in the Rows or Columns field wells alphabetically or by a metric in ascending or descending order. | [Sorting visual data in Amazon Quick](sorting-visual-data.md) [Sorting pivot tables in Quick](sorting-pivot-tables.md) | | Performing field aggregation | Yes | You must apply aggregation to the field or fields you choose for the value. You can't apply aggregation to the fields that you choose for the rows or columns. If you choose to create a multi-measure pivot table, you can apply different types of aggregation to the different measures. For example, you can show the sum of the sales amount and the maximum discount amount. | [Changing field aggregation](changing-field-aggregation.md) | | Adding drill-downs | No | | [Adding drill-downs to visual data in Quick Sight](adding-drill-downs.md) | | Showing and hiding totals and subtotals | Yes | You can show or hide totals and subtotals for rows and columns. Metrics automatically roll up to show subtotals when you collapse a row or column. If you use a table calculation, use aggregates to display roll-ups. | | | Exporting or copying data | Yes | You can export all of the data to a CSV file. You can select and copy the content of the cells. | [Exporting data from visuals](exporting-data.md) | | Conditional formatting | Yes | You can add conditional formatting for values, subtotals and totals. | [Conditional formatting on visual types in Quick](conditional-formatting-for-visuals.md) | **Topics** # Creating a pivot table Use the following procedure to create a pivot table. **To create a pivot table** 1. On the analysis page, choose the **Visualize** icon on the tool bar. 1. On the **Visuals** pane, choose **\$1 Add**, and then choose the pivot table icon. 1. From the **Fields list** pane, choose the fields that you want to include. Amazon Quick automatically places these into the field wells. To change the placement of a field, drag it to the appropriate field wells. Typically, you use dimension or measure fields as indicated by the target field well. If you choose to use a dimension field as a measure, the **Count** aggregate function is automatically applied to it to create a numeric value. + To create a single-measure pivot table, drag a dimension to the **Rows** field well, a dimension to the **Columns** field well, and a measure to the **Values** field well. + To create a multi-measure pivot table, drag a dimension to the **Rows** field well, a dimension to the **Columns** field well, and two or more measures to the **Values** field well. + To create a clustered pivot table, drag one or more dimensions to the **Rows** field well, one or more dimensions to the **Columns** field well, and a measure to the **Values** field well. You can also select multiple fields for all of the pivot table field wells if you want to. Doing this combines the multi-measure and clustered pivot table approaches. **Note** To view roll-ups for calculated fields, make sure that you are using aggregates. For example, a calculated field with `field-1 / field-2 `doesn't display a summary when rolled up. However, `sum(field-1) / sum(field-2) `does display a roll-up summary. ## Choosing a layout When you create a pivot table in Amazon Quick, you can further customize the way your data is presented with Tabular and Hierarchy layout options. For pivot tables that use a tabular layout, each row field is displayed in its own column. For pivot tables that use a hierarchy layout, all row fields are displayed in a single column. Indentation is used to differentiate row headers of different fields. To change the layout of a pivot table visual, open the **Format visual** menu of the pivot table that you want to change and choose the layout option that you want from the **Pivot options** section. Depending on the layout that you choose for your pivot table visual, different formatting options are available. For more information about formatting differences between tabular and hierarchy pivot tables, see [Table and pivot table formatting options in Quick](format-tables-pivot-tables.md). # Orienting pivot table values Display orientation You can choose to display a pivot table in a columnar or row-based format. Columnar is the default. When you change to a row-based format, a column with the value name is added to the right of the row header column. **To change a pivot table format** 1. On the analysis page, choose the pivot table visual that you want to edit. 1. Expand the **Field wells** pane by choosing the field wells at the top of the visual. 1. On the **Values** field well, choose one of the following options: + Choose **Column** for a columnar format. + Choose **Row** for a row format. **Note** If you use only one metric, you can eliminate the repeated header by formatting the visual and styling it with the **Hide single metric** option. # Expanding and collapsing pivot table clusters If you are using grouped columns or rows in a pivot table, you can expand or collapse a group to show or hide its data in the visual. **To expand or collapse a pivot table group** 1. On the analysis page, choose the pivot table visual that you want to edit. 1. Choose one of the following: + To collapse a group, choose the collapse icon near the name of the field. + To expand a group, choose the expand icon near the name of the field. The collapse icon shows a minus sign. The expand icon shows a plus sign. In the following screenshot, `Customer Region` and the `Enterprise` segment are expanded, and `SMB` and `Startup` are collapsed. When a group is collapsed, its data is summarized in the row or column. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/pivot-table-collapse.png) # Showing and hiding pivot table columns in Quick Showing and hiding pivot table columns By default, all columns, rows, and their field values appear when you create a pivot table. You can hide columns and rows that you don't want to appear in the pivot table without changing the pivot table values. When you have more than one measure in the pivot table, you can also hide values. At any time, you can choose to show any hidden fields in the pivot table. When you publish the visual as part of a dashboard, anyone who subscribes to the dashboard can export the pivot table to a comma-separated value (CSV) or Microsoft Excel file. They can choose to export only the visible fields, or all fields. For more information, see [Exporting data from a dashboard to a CSV](export-or-print-dashboard.md#export-dashboard-to-csv). **To hide a column or row in a pivot table** 1. In your analysis, select the pivot table visual that you want to work with. 1. Choose the three-dot menu in the **Rows**, **Columns** or **Values** field wells, and then choose **Hide**. **To show all hidden fields in a pivot table** 1. In your analysis, select the pivot table visual that you want to work with. 1. Choose any field in the **Fields well** and choose **Show all hidden fields**. # Sorting pivot tables in Quick Sorting pivot tables In Amazon Quick, you can sort values in a pivot table by fields in the **Rows** and **Columns** field wells or quickly by column headers in the pivot table. In pivot tables, you can sort rows and columns independently of each other in alphabetical order, or by a measure. **Note** You can't run Total, Difference, and Percent Difference table calculations when a pivot table is being sorted by a measure. For more information about using table calculations in pivot tables, see [Using table calculations in pivot tables](working-with-calculations.md). ## Understanding sorting in pivot tables When you have multiple panes in a pivot table, sorting is applied to each pane independently. For example, the `Segment` column in the pivot table on the left is being sorted in ascending order by `Cost`. Given that there are multiple panes, the sort starts over for each pane and the rows within each pane (for `Segment`) are ordered by lowest to highest cost. The table on the right has the same sort applied, but the sort is being applied across the entire table, as shown following. ![\[Image of a pivot table with a sort highlighted in red.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sorting-pivot-tables2.png) When you apply multiple sorts to a pivot table, sorting is applied from the outside dimension to the inside dimension. Consider the following example image of a pivot table. The `Customer Region` column is sorted by `Cost` in descending order (as shown in orange). The `Channel` column is sorted by Revenue Goal in ascending order (as shown in blue). ![\[Image of a pivot table showing two measure value columns sorted.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sorting-pivot-tables3.png) ## Sorting pivot tables using row or column headers Sorting pivot tables using row or column headers Use the following procedure to sort a pivot table using Row or Column headers. **To sort values in a tabular pivot table using table headers** 1. In a tabular pivot table chart, choose the header that you want to sort. 1. For **Sort by**, choose a field to sort by and a sort order. You can sort dimension fields alphabetically a–z or z–a, or you can sort them by a measure in ascending or descending order. ![\[Animated .gif file of sorting values in a pivot table using column headers.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sorting-pivot-table7.gif) ## Sorting pivot tables using value headers Sorting pivot tables using value headers Use the following procedure to sort a pivot table using value headers. **To sort a pivot table using value headers** 1. In a pivot table chart, choose the value header that you want to sort. 1. Choose **Ascending** or **Descending**. ![\[Animated .gif file of sorting values in a pivot table using value headers.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sorting-pivot-tables-value.gif) Sorting by value headers in a pivot table also works on subtotals. ## Sorting tabular pivot tables using the field wells Sorting tabular pivot tables using the field wells Use the following procedure to sort values in a tabular pivot table using the field wells. **To sort values in a tabular pivot table using the field wells** 1. On the analysis page, choose the tabular pivot table that you want to sort. 1. Expand the **Field wells**. 1. In the **Rows** or **Columns** field well, choose the field that you want to sort, and then choose how you want to sort the field for **Sort by**. You can sort dimension fields in the **Rows** or **Columns** field wells alphabetically from a–z or z–a, or you can sort them by a measure in ascending or descending order. You also have the option to collapse all or expand all rows or columns for the field you choose in the field well. You can also remove the field, or to replace it with another field. + To sort a dimension field alphabetically, hover your cursor over the field in the **Rows** or **Columns** field well, and then choose the a–z or z–a sort icon. ![\[Image of a field in the Rows field well with the sort by field and alphabetical sort icons indicated in red squares.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sorting-pivot-tables1.png) + To sort a dimension field by a measure, hover your cursor over the field in the **Rows** or **Columns** field well. Then choose a measure from the list, and then choose the ascending or descending sort icon. ![\[Image of a field in the Rows field well with the sort by field and sort icons indicated in red squares.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sorting-pivot-tables4.png) Or, if you want more control over how the sort is applied to the pivot table, customize the sort options. **To create a sort using the **sort options**** 1. On the analysis page, choose the pivot table that you want to sort. 1. Expand **Field wells**. 1. Choose the field that you want to sort in the **Rows** or **Columns** field well, and then choose **Sort options**. 1. In the **Sort options** pane that opens at left, specify the following options: 1. For **Sort by**, choose a field from the drop-down list. 1. For **Aggregation**, choose an aggregation from the list. 1. For **Sort order**, select **Ascending** or **Descending**. 1. Choose **Apply**. ## Sorting hierarchy pivot tables using the field wells For tabular pivot tables, each field in the **Rows ** field well has a separate title cell. For hierarchy pivot tables, all row fields are displayed in a single column. To sort, collapse, and expand these row fields, select the **Rows** label to open the **Combined row fields** menu and choose the option that you want. Each field in a hierarchy pivot table can be individually sorted from the **Combined row fields** menu. ![\[Image of the Combined row fields menu.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/pivot-table-combined-row-fields-menu.png) More advanced formatting options such as **Hide** and **Remove** are available from the field well menus. # Using table calculations in pivot tables You can use table calculations to apply statistical functions to pivot table cells that contain measures (numeric values). Use the following sections to understand which functions you can use in calculations, and how to apply or remove them. The data type of the cell value automatically changes to work for your calculation. For example, say that you apply the **Rank** function to a currency data type. The values display as integers rather than currency, because rank isn't measured as currency. Similarly, if you apply the **Percent difference** function instead, the cell values display as percentages. **Topics** + [ # Adding and deleting pivot table calculations ](adding-a-calculation.md) + [ # Functions for pivot table calculations ](supported-functions.md) + [ # Ways to apply pivot table calculations ](supported-applications.md) # Adding and deleting pivot table calculations Use the following procedures to add, modify, and remove table calculation on a pivot table. **Topics** + [ # Adding a pivot table calculation ](add-a-calculation.md) + [ # Changing how a calculation is applied ](change-how-a-calculation-is-applied.md) + [ # Removing a calculation ](remove-a-calculation.md) # Adding a pivot table calculation Use the following procedure to add a table calculation to a pivot table. **To add a table calculation to a pivot table** 1. Expand the **Field wells** pane by choosing the field wells near the bottom of the visual. 1. Choose the field in the **Values** well that you want to apply a table calculation to, choose **Add table calculation**, and then choose the function to apply. **Note** You can't run Total, Difference, and Percent Difference table calculations when a pivot table is being sorted by a measure. To use these table calculations, remove the sort from the pivot table. # Changing how a calculation is applied Use the following procedure to change the way a table calculation is applied to a pivot table. **To change the way a table calculation is applied to a pivot table** 1. Expand the **Field wells** pane by choosing field wells at the top of the visual. 1. Choose the field in the **Values** well that has the table calculation that you want to change, choose **Calculate as**, and then choose the way that you want the calculation applied. # Removing a calculation Use the following procedure to remove a table calculation from a pivot table. **To remove a table calculation from a pivot table** 1. Expand the **Field wells** pane by choosing the field wells near the bottom of the visual. 1. Choose the field in the **Values** well that you want to remove the table calculation from, and then choose **Remove**. # Functions for pivot table calculations You can use the following functions in pivot table calculations. **Topics** + [ ## Running total ](#running-total) + [ ## Difference ](#difference) + [ ## Percentage difference ](#percent-difference) + [ ## Percent of total ](#percent-of-total) + [ ## Rank ](#rank) + [ ## Percentile ](#percentile) You can apply functions listed to the following data: ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total1.png) ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total1.png) ## Running total The **Running total** function calculates the sum of a given cell value and the values of all cells prior to it. This sum is calculated as `Cell1=Cell1, Cell2=Cell1+Cell2, Cell3=Cell1+Cell2+Cell3`, and so on. Applying the **Running total** function across the table rows, using **Table across** for **Calculate as**, gives you the following results. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total2.png) ## Difference The **Difference** function calculates the difference between a cell value and value of the cell prior to it. This difference is calculated as `Cell1=Cell1-null, Cell2=Cell2-Cell1, Cell3=Cell3-Cell2,` and so on. Because `Cell1-null = null`, the Cell1 value is always empty. Applying the **Difference** function across the table rows, using **Table across** for **Calculate as**, gives you the following results. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/difference.png) ## Percentage difference The **Percentage Difference** function calculates the percent difference between a cell value and the value of the cell prior to it, divided by the value of the cell prior to it. This value is calculated as `Cell1=(Cell1-null)/null, Cell2=(Cell2-Cell1)/Cell1, Cell3=(Cell3-Cell2)/Cell2,` and so on. Because `(Cell1-null)/null = null`, the Cell1 value is always empty. Applying the **Percentage Difference** function across the table rows, using **Table across** for **Calculate as**, gives you the following results. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/percentage-difference.png) ## Percent of total The **Percent of Total** function calculates the percentage the given cell represents of the sum of all of the cells included in the calculation. This percentage is calculated as `Cell1=Cell1/(sum of all cells), Cell2=Cell2/(sum of all cells),` and so on. Applying the **Percent of Total** function across the table rows, using **Table across** for **Calculate as**, gives you the following results. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/percent-of-total.png) ## Rank The **Rank** function calculates the rank of the cell value compared to the values of the other cells included in the calculation. Rank always shows the highest value equal to 1 and lowest value equal to the count of cells included in the calculation. If there are two or more cells with equal values, they receive the same rank but are considered to take up their own spots in the ranking. Thus, the next highest value is pushed down in rank by the number of cells at the rank above it, minus one. For example, if you rank the values 5,3,3,4,3,2, their ranks are 1,3,3,2,3,6. For example, suppose that you have the following data. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rank.png) Applying the **Rank** function across the table rows, using **Table across** for **Calculate as**, gives you the following results. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rank2.png) ## Percentile The **Percentile** function calculates the percent of the values of the cells included in the calculation that are at or below the value for the given cell. This percent is calculated as follows. ``` percentile rank(x) = 100 * B / N Where: B = number of scores below x N = number of scores ``` Applying the **Percentile** function across the table rows, using **Table across** for **Calculate as**, gives you the following results. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/percentile.png) # Ways to apply pivot table calculations You can apply table calculations in the ways described following. Table calculations are applied to only one field at a time. Thus, if you have a pivot table with multiple values, calculations are only applied to the cells representing the field that you applied the calculation to. **Topics** + [ ## Table across ](#table-across) + [ ## Table down ](#table-down) + [ ## Table across down ](#table-across-down) + [ ## Table down across ](#table-down-across) + [ ## Group across ](#group-across) + [ ## Group down ](#group-down) + [ ## Group across down ](#group-across-down) + [ ## Group down across ](#group-down-across) ## Table across Using **Table across** applies the calculation across the rows of the pivot table, regardless of any grouping. This application is the default. For example, take the following pivot table. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sample-pivot.png) Applying the **Running total** function using **Table across** gives you the following results, with row totals in the last column. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/table-across.png) ## Table down Using **Table down** applies the calculation down the columns of the pivot table, regardless of any grouping. Applying the **Running total** function using **Table down** gives you the following results, with column totals in the last row. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/table-down.png) ## Table across down Using **Table across down** applies the calculation across the rows of the pivot table, and then takes the results and reapplies the calculation down the columns of the pivot table. Applying the **Running total** function using **Table across down** gives you the following results. In this case, totals are summed both down and across, with the grand total in the lower-right cell. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total-across-down.png) In this case, suppose that you apply the **Rank** function using **Table across down**. Doing so means that the initial ranks are determined across the table rows and then those ranks are in turn ranked down the columns. This approach gives you the following results. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rank-table-across-down.png) ## Table down across Using **Table down across** applies the calculation down the columns of the pivot table. It then takes the results and reapplies the calculation across the rows of the pivot table. You can apply the **Running total** function using **Table down across** to get the following results. In this case, totals are summed both down and across, with the grand total in the lower-right cell. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total-down-across.png) You can apply the **Rank** function using **Table down across** to get the following results. In this case, the initial ranks are determined down the table columns. Then those ranks are in turn ranked across the rows. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rank-table-down-across.png) ## Group across Using **Group across** applies the calculation across the rows of the pivot table within group boundaries, as determined by the second level of grouping applied to the columns. For example, if you group by field-2 and then by field-1, grouping is applied at the field-2 level. If you group by field-3, field-2, and field-1, grouping is again applied at the field-2 level. When there is no grouping, **Group across** returns the same results as **Table across**. For example, take the following pivot table where columns are grouped by `Service Line` and then by `Consumption Channel`. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sample-pivot.png) You can apply the **Running total** function using **Group across** to get the following results. In this case, the function is applied across the rows, bounded by the columns for each service category group. The `Mobile` columns display the total for both `Consumption Channel` values for the given `Service Line`, for the `Customer Region` and `Date` (year) represented by the given row. For example, the highlighted cell represents the total for the `APAC` region for `2012`, for all `Consumption Channel` values in the `Service Line` named `Billing`. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/group-across.png) ## Group down Using **Group down** applies the calculation down the columns of the pivot table within group boundaries, as determined by the second level of grouping applied to the rows. For example, if you group by field-2 and then by field-1, grouping is applied at the field-2 level. If you group by field-3, field-2, and field-1, grouping is again applied at the field-2 level. When there is no grouping, **Group down** returns the same results as **Table down**. For example, take the following pivot table where rows are grouped by `Customer Region` and then by `Date` (year). ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sample-pivot.png) You can apply the **Running total** function using **Group down** to get the following results. In this case, the function is applied down the columns, bounded by the rows for each `Customer Region` group. The `2014` rows display the total for all years for the given `Customer Region`, for the `Service Line` and `Consumption Channel` represented by the given column. For example, the highlighted cell represents the total the `APAC` region, for the `Billing` service for the `Mobile` channel, for all the `Date` values (years) that display in the report. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/group-down.png) ## Group across down Using **Group across down** applies the calculation across the rows within group boundaries, as determined by the second level of grouping applied to the columns. Then the function takes the results and reapplies the calculation down the columns of the pivot table. It does so within group boundaries as determined by the second level of grouping applied to the rows. For example, if you group a row or column by field-2 and then by field-1, grouping is applied at the field-2 level. If you group by field-3, field-2, and field-1, grouping is again applied at the field-2 level. When there is no grouping, **Group across down** returns the same results as **Table across down**. For example, take the following pivot table where columns are grouped by `Service Line` and then by `Consumption Channel`. Rows are grouped by `Customer Region` and then by `Date` (year). ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sample-pivot.png) You can apply the **Running total** function using **Group across down** to get the following results. In this case, totals are summed both down and across within the group boundaries. Here, these boundaries are `Service Line` for the columns and `Customer Region` for the rows. The grand total appears in the lower-right cell for the group. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total-group-across-down.png) You can apply the **Rank** function using **Group across down** to get the following results. In this case, the function is first applied across the rows bounded by each `Service Line` group. The function is then applied again to the results of that first calculation, this time applied down the columns bounded by each `Customer Region` group. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rank-group-across-down.png) ## Group down across Using **Group down across** applies a calculation down the columns within group boundaries, as determined by the second level of grouping applied to the rows. Then Amazon Quick takes the results and reapplies the calculation across the rows of the pivot table. Again, it reapplies the calculation within group boundaries as determined by the second level of grouping applied to the columns. For example, if you group a row or column by field-2 and then by field-1, grouping is applied at the field-2 level. If you group by field-3, field-2, and field-1, grouping is again applied at the field-2 level. When there is no grouping, **Group down across** returns the same results as **Table down across**. For example, take the following pivot table. Columns are grouped by `Service Line` and then by `Consumption Channel`. Rows are grouped by `Customer Region` and then by `Date` (year). ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sample-pivot.png) You can apply the **Running total** function using **Group down across** to get the following results. In this case, totals are summed both down and across within the group boundaries. In this case, these are `Service Category` for the columns and `Customer Region` for the rows. The grand total is in the lower-right cell for the group. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/running-total-group-across-down.png) You can apply the **Rank** function using **Group down across** to get the following results. In this case, the function is first applied down the columns bounded by each `Customer Region` group. The function is then applied again to the results of that first calculation, this time applied across the rows bounded by each `Service Line` group. ![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rank-group-down-across.png) # Pivot table limitations The following limitations apply to pivot tables: + You can create pivot tables with up to 500,000 records. + You can add any combination of row and column field values that add up to 40. For example, if you have 10 row field values, then you can add up to 30 column field values. + You can create pivot table calculations only on nonaggregated values. For example, if you create a calculated field that is a sum of a measure, you can't also add a pivot table calculation to it. + If you are sorting by a custom metric, you can't add a table calculation until you remove the custom metric sort. + If you are using a table calculation and then add a custom metric, you can't sort by the custom metric. + Totals and subtotals are blank for table calculations on metrics aggregated by distinct count. # Pivot table best practices It's best to deploy a minimal set of rows, columns, metrics, and table calculations, rather than offering all possible combinations in one pivot table. If you include too many, you risk overwhelming the viewer and you can also run into the computational limitations of the underlying database. To reduce the level of complexity and reduce the potential for errors, you can take the following actions: + Apply filters to reduce the data included in for the visual. + Use fewer fields in the **Row** and **Column** field wells. + Use as few fields as possible in the **Values** field well. + Create additional pivot tables so that each displays fewer metrics. In some cases, there's a business need to examine many metrics in relation to each other. In these cases, it can be better to use multiple visuals on the same dashboard, each showing a single metric. You can reduce the size of the visuals on the dashboard, and colocate them to form a grouping. If a decision the viewer makes based on one visual creates the need for a different view, you can deploy custom URL actions to launch another dashboard according to the choices made by the user. It's best to think of visuals as building blocks. Rather than using one visual for multiple purposes, use each visual to facilitate one aspect of a larger business decision. The viewer should have enough data to make a well-informed decision, without being overwhelmed by the inclusion of all possibilities.