The pivot table feature allows you to reorganize and summarize selected columns and rows of data to create a report within the Grid view. Pivot tables can be used in a regular assortment, or while reviewing multiple assortments in a Rollup.
Creating a pivot table
- Select the Columns tab in the assortment sidebar to open your pivot settings.
-
Select Pivot Mode.
- Drag and drop the fields you'd like to select for your Row Groups, Values, and/or Column Labels.
-
Row Groups: These will be the fields you want to see cumulative metrics for.
- After applying a group, you'll automatically see a number in parentheses, which indicates the number of rows in each group.
- You can drag more than one field to see a nested analysis, e.g. Vendor > Delivery/Month or Delivery > Class. If you've applied multiple fields, you can expand each group to see the sub-sets.
-
Values: This will be the metric that is tabulated for your report.
- After dragging the field into the Values, "Sum" will be the default metric, which will show a sum total of all rows in each group.
- You can click to select a different type of metric, including Count, Count (unique), or Average.
Note: Count (unique) shows you the Count of unique values in a group, instead of the Count of rows in that group. - You can click to select a different type of metric, including Count or Average.
- For sums and averages you'll typically want to use a number type field, like units or total cost/retail.
-
Column Labels: If you want to see your values tabulated across more than one set of data all in one view, drag a field here.
- For example, to see your totals by month going across the page, drag your Month/Delivery field into the columns to see each month's total in a separate column.
Review percent to total in your pivot table
When planning your buy, it can be important to review the percentage or contribution each group represents of the total buy, so that you can make sure your buy is balanced.
After dragging an assortment field into the "Values" area, click on that field to select Sum(%) or Count(%) / Count(unique) your value type.
- Sum(%) will show the sum for all items in the group and the percent of that sum total.
- Count(%) will show the number of rows and the percent those rows represent. Each row will be counted once; if you have the same item on multiple rows, it will be counted for each row.
- Count (unique) shows you the Count of unique values in a group, instead of the total Count of rows in that group.