Aggregation Rules of Pivot Table

Introduction

Stats are common tools for processing and displaying data and the Aggregation function in Measure of pivot tables is frequently used in statistics.

In order to acquire more accurate analysis results, you need to know the aggregation rules of pivot tables in detail.

Aggregation Rules

1. When fields in a form are directly aggregated as the measure of a pivot table, the aggregation rule is to calculate first and then to aggregate.

2. When adding a calculated field as a measure and a Math function is nested in the formula, the aggregation rule is to aggregate first according to the function and then to aggregate (that is, aggregating before calculating). If there is no Math function nested in the formula, the aggregation rule is to calculate first and then to aggregate (that is, calculating before aggregating).

Aggregation Methods of Measure

Aggregation methods in Measure include:

1. When number fields of a form are used as measures of the pivot table, the aggregation method supports Sum, AVG, Max, Min, Count, and Dedupe. (The default is Sum.)

2. When non-number fields of a form are used as measures, the aggregation method supports Count/Dedupe. (The default is Count.)

3. When number fields of a subform are used as measures, the aggregation method supports Sum, AVG, Max, Min, and Count. (The default is Sum.)

4. When non-number fields of a subform are used as measures, the aggregation method only supports Count.

5. The aggregation rows/columns of the pivot table are aggregated according to the logic of measures.

Note: Calculated fields cannot support aggregation of measures.

Aggregation Rule of Calculated Field

The calculation rules of calculating before aggregating and aggregating before calculating will be explained in detail.

Calculating before Aggregating

Generally, the aggregation method of measures that have not been operated by math functions is to calculate before aggregating. This is also the most common method of aggregation.

For example, to calculate the total sales, use "the sales volume * unit price" to calculate the sales volume of each order, and then set this field as a measure to sum and aggregate data. Finally, the total sales can be aggregated as follows:

The formula for the calculated field is as follows:

Aggregating before Calculating

If calculated fields are added as measures, such as a Math function nested in the formula, the aggregation method is to aggregate first according to the function and then to calculate (that is, aggregating before calculating).

1. Calculate the completion rate

For example, calculate the completion rate for each member. Since members enter the completion quantity every day, so when you calculate the completion rate, you need to sum the completion quantity in history and then calculate the completion rate.

The formula for calculated fields is as follows:

2. Daily completion quantity per capita

The number of daily completion quantity per capita is equal to that of total completion volume for the day/that of people for the day. You need to use the SUM function to sum the daily completion quantity, and then use the COUNT function to count the number of people. The formula of daily completion quantity per capita is SUM(Daily Completion Quantity)/COUNT(Employee Name).

The formula for calculated fields is as follows:

Notes

1. If the value of the field as a measure is empty in one record, this record will be filtered and not be displayed.

2. When subfields are included in dimensions, the measure only supports subfields.

3. Multiple Multi Select fields cannot be used as dimensions at the same time.

4. Fields from different subforms cannot be aggregated.

5. When there are Multi Select fields of the form in the dimension, subfields cannot set as measures.

6. The total amount of pivot tables access data is up to 16 MB.

7. When the data source is a form, data alert can only be set in charts when the data permission is All Form Data. Area charts, pie charts, and radar charts don't support data alert.

Troubleshooting of Wrong Results of Adding Calculated Fields

1. According to the content of aggregation logic and the expected aggregation result, check whether there is any problem in the setting of measures and formulas;

2. In the data source form, check whether the value of the field as the measure is empty. The data will be filtered out and be not aggregated.