Understanding pivot table calculation logic

This feature is currently available only in Dashboard builder and requires an Expert user account with the Dashboard Extension.

How pivot table calculations work

A pivot table is a flexible framework for defining which respondents are included, which survey variable is analyzed, and how the result is calculated.

Every cell in a pivot table is determined by three elements:

1. Base variable defines the survey data used as the basis for the calculation.
2. Aggregator defines how the data is summarized, for example, as a count, sum, mean, or with a custom formula. Custom formulas let you define calculation logic when standard aggregators don’t meet your needs.
3. Conditions (filters) define the subset of data included in the calculation. Can be set on report, table, column or row level.

Base variables in columns or rows

As a general rule, define the base variable explicitly in either the rows or the columns, not in both, unless you intentionally want to override it using advanced settings. Keeping the base variable in one location makes the calculation easier to understand and helps prevent unintended overrides or duplicated configuration.

• Set it in columns when all rows share the same source variable and the column should apply a consistent calculation across them
• Set it in the rows when different rows analyze different questions or items and the columns should reuse each row’s variable through Take from row

Calculation logic in an Age Distribution table

The following Age Distribution table shows how these calculation elements work together.

The table groups respondents by age and displays:

• The number of respondents in each age group
• The percentage of respondents represented by each group

Count column

Base variable

The Count column uses Age as its base variable. This tells Survalyzer which survey variable provides the values for the calculation.

In the survey raw data, the Age variable contains the answer selected by each respondent.

Aggregator

The column uses Count as its aggregator, which means that after the condition for a row is applied, Count returns the number of matching responses. For example, for the row younger than 18 years, the result is the number of respondents whose Age value matches this answer option.

Percentage column

The Percentage column is a calculated column that uses a custom formula to display results as percentages.

Base variable

When a column uses a custom formula, the base variable is ignored in the calculation. A base variable is still selected by default, but it does not affect the result.

Custom formula

The in % column shows the share of respondents represented by each row. It uses the following custom formula:

CellValue(null,"Column2").Value/DataSourceResult.ResponseCount

The formula consists of two parts:

• CellValue(null,"Column2").Value returns the value from Column2 for the current row. null refers to the current row. If you specify a row instead, the formula references that specific row.
• DataSourceResult.ResponseCount returns the total number of responses available in the current data source scope (all interviews considered in this table, after report & table filters, based on base variables defined in columns and/or rows).

The formula divides the current row value by the total response count to calculate the percentage represented by that row.

Conceptually, null works like a relative row reference in Excel. Specifying a row fixes the reference instead.

Assuming we already have an equivalent pivot table in excel with count column, the same percentage calculation column formula can be expressed in an Excel as:

=$B2/SUM($B$2:$B$6)

For more information about available calculation objects and syntax, see Calculation code to build custom formulas.

Rows and conditions

Base variable

The Age base variable is already defined in the Count column. The rows therefore use Take from column instead of defining the same base variable again.

This keeps the calculation structure consistent and avoids duplicate configuration.

For more advanced tables, a specific row can use a different base variable when it needs to analyze a different survey variable. By default, row base variable will be prioritized, unless “use column base variable for calculations” column setting is selected (setting visible only when there are base variables defined both in column and row).

Conditions

Each row has a condition that includes respondents who selected a specific Age answer option. For example, the first row includes respondents who selected 1 – younger than 18 years. The Age base variable identifies the source variable, while the row condition determines which values are included in the calculation.

A condition can use any survey variable, not only the base variable. This means you can filter the calculation by variables that aren’t used in the aggregation.

Conditions can also use dynamic references. For example, a column condition can include only respondents for whom the row variable equals 5. The condition then adapts automatically to the variable used in each row.

In Excel terms, a condition works like filtering the source data before the PivotTable calculates the results.

What pivot table calculations exclude by default

By default, Survalyzer excludes N/A (Not Applicable) answers and Missings (unanswered or hidden questions) from aggregator calculations such as Count, Sum, or Mean.

This means that standard aggregators only consider respondents who actively selected a regular answer option. Respondents who selected N/A or did not answer the question at all are not counted.

Survalyzer and Excel PivotTable concepts

The following table summarizes the main Pivot Table concepts used in Survalyzer and their closest equivalents in Excel PivotTables.