Pivot Table
Introduction
Feature(s)
A pivot table, also called an aggregation table, is a powerful tool to sort and aggregate data. Unlike other charts, pivot tables allow you to set multiple different dimensions, like row and column dimensions, and multiple measures.
- Dimension: It refers to your ways to analyze data. For example, year can be the time dimension and Tokyo the geographical dimension.
- Measure: It quantifies a dimension. It can be a certain value or a proportion that needs to be calculated such as sales figures, sales, and growth rate.
Application Scenario(s)
- Track inventory during PSI (purchase, sale, inventory) management.
- Aggregate sales by regions.
- Aggregate academic results by class and grade.
- …
The following is a sales pivot table. The sales can be aggregated to obtain the total sales of each order.
Basic Requirement(s)
To design a pivot table, you need to add at least one dimension and measure fields.
Chart | Dimension | Measure |
Pivot Table | ≥ 1 | ≥ 1 |
Setting Procedure
A dashboard is used for statistical analysis based on existing data. It allows you to aggregate and calculate data from various sources such as forms, the data factory, and aggregate tables. Note that you need to input data into these sources before the data analysis.
Adding a Chart for Statistics
Select a dashboard, click Stats, and select a form (such as Order Form) as the data source.
Selecting a Chart Type
Select Pivot Table under Chart Type.
Setting Row/Column Dimension(s)
Drag the fields you need to analyze, such as Order Number and Customer Name, from Fields to Dim.(Row)/ Dim.(Col). Then set Dim.(Row) and/or Dim.(Col).
Note:
At least one dimension should be set in a pivot table.
You can further set Display Name, Aggregation, Data Display Format, Sort, and others for the fields.
Select a dimension field, click , and select an item from the drop-down list.
Setting Measure(s)
A measure is a column with numerical data. It is used to measure target values. In one pivot table, you can add multiple measures.
For Measure, select fields under Fields, and then set them as measures, or add new calculated fields.
For example, the total amount is quite large and you want to display it in millions. To make it happen, add a calculated field, and add a formula to divide the total by 1,000,000. Then drag the field to Measure.
Setting Aggregation
Aggregation means the way you aggregate data.
1. Set the aggregation for dimension fields.
Choose a dimension field, and select Aggregation from the drop-down list.
For example, Date&Time fields can be aggregated by Year, Year-Quarter, Year-Month, Year-Week, and Year-Month-Day.
2. Set the aggregation for measure fields.
Choose a measure field, and select Aggregation from the drop-down list.
Then choose Sum, AVG, Max, Min, Count, or Dedupe from Aggregation. If the measure field is not a number field, you can only choose Count and Dedupe.
Setting Display Serial No.
Select a Dim.(Row) field whose serial numbers you want to display, and click Display Serial No..
For example, display the serial number of Customer Name, and display the total sales in an ascending order.
If it is unnecessary to display serial number, ignore this step.
Setting Aggregation Row
If you need to set an aggregation row, tick the items below to decide where the row will be displayed.
If not, untick it.
Setting Subtotal
Subtotal allows you to aggregate data for each dimension in a pivot table.
For example, in the sales scenario, subtotals can be used to aggregate sales revenue by regions and stores.
If there are two or more dimensions, multiple subtotals will appear on the table.
Note:
1. Subtotals are only supported for rows instead of columns now.
2. All the aggregation methods for Measure are available for Subtotal except Dedupe and AVG.
3. You cannot set Data Alert and Conditional Formatting for Subtotal.
4. Functions (including SUM, AVERAGE, COUNT, MAX, and MIN) are added for calculated fields. If you add a calculated field to Measure, subtotals for that field will not be calculated. Instead, they will be denoted with a hyphen (-).
Demonstration
Depending on the dimensions and measures, different aggregating methods brings different results.