When using custom calculations in pivot tables or summary tables, you may encounter the error:
"The expression is returning a list of values. Please use summarize functions to return a summarized value for each group."
In Exploratory, it is possible to select Custom Calculation for values in pivot tables or summarize tables and write formulas to calculate metrics.
However, when using custom calculations, you may sometimes see the error:
"The formula returns values that span multiple rows. Please use an aggregation function so that a single aggregated value is returned per group."
This occurs because custom calculations only support formulas that return a single aggregated value.
For example, suppose the original dataset has columns for Sales and Order_Count.
In this case, simply writing Sales / Order_Count
will result in an error because Sales and Order_Count span multiple rows.
Therefore, even if the data in question consists of just one row, you must use aggregation functions in your custom calculation. For instance, write the formula as sum(Sales) / sum(Order_Count)
so that a single aggregated value is returned per group.