What is a Pivot Table?
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 of analyzing data. For example, the 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.
When to Use a Pivot Table?
Track inventory during PSI (purchase, sale, inventory) management.
Aggregate sales by region.
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 |
What does it Look Like?
Depending on the dimensions and measures, different aggregating methods brings different results.
How to Set up?
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.
Step 1 Adding a Chart for Statistics
Select a dashboard, click Stats, and select a form (such as Order Form) as the data source.
Step 2 Selecting a Chart Type
Select Pivot Table under Chart Type.
Step 3 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.
Step 4 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.
Step 5 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 measured field is not a number field, you can only choose Count and Dedupe.
Step 6 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 ascending order.
If it is unnecessary to display the serial number, ignore this step.
Step 7 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.
Step 8 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 (-).
I have finished reading. 🏆
👉I can't wait to Try for myself.
👉I need more HELP in Discord Forum.