Loading

Grouped Field Shows Incorrect Row Count, Sum, or Calculation in CRM Analytics Due to Null Values

Data pubblicazione: Jun 29, 2026
Descrizione

Overview
When exploring data in a CRM Analytics Lens, you can group by a field to break down results by the values in that field. Grouping can also be performed using CRM Analytics SAQL (Salesforce Analytics Query Language). When a record does not have a value in that field (the field is null), that record is excluded from grouped results. This can result in perceived discrepancies in row counts, sums, or other calculations.
Note: Many of the details below also apply to sorting functionality in CRM Analytics. For additional details, see Limitations of Null Values in Dimensions in CRM Analytics.
Example — How Null Values Affect Grouped Results
Consider a dataset with five records. The Region field is null for record ID 2:

  • ID 1, Region: NW, Value: 10
  • ID 2, Region: (null), Value: 5
  • ID 3, Region: NE, Value: 10
  • ID 4, Region: SE, Value: 10
  • ID 5, Region: SW, Value: 10

Without grouping, the Sum of Value displays 45 (all five records included).
When grouped by Region, the Sum of Value shows:

  • NE: 10
  • NW: 10
  • SE: 10
  • SW: 10

The grouped total is 40, not 45. The record with the null Region field is excluded from the grouped calculation. This is expected CRM Analytics behavior.

Risoluzione

Why This Happens and How to Fix It
CRM Analytics excludes null values when grouping by a dimension. To ensure null records are included in your calculations, you can either assign a default value to the null field or use an ungrouped lens for the calculation.
Option A — Add a defaultValue in the Dataflow or Schema
Use the defaultValue override in the dataflow sfdcDigest transformation or in the external data schema to specify a value for records where the field is null.
For example, in the sfdcDigest transformation for the Region field, set defaultValue to "Unknown" so that records with no Region are grouped under "Unknown" instead of being excluded.
Alternatively, use the coalesce() SAQL function to replace null values inline. For example, the following SAQL query groups a dataset by Year and calculates an average price while treating null values as zero:

  • Load the dataset

  • Group by Year

  • For each group, generate the Year value and calculate coalesce(sum(Amount)/sum(Quantity), 0) as AvgPrice

The coalesce() function returns the first non-null value in the list. By passing 0 as the second argument, it replaces null calculation results with 0 instead of excluding the row.
Option B — Use an Ungrouped Lens for Dashboard Calculations
If you display the calculated value on a CRM Analytics dashboard, include a widget powered by an ungrouped version of the lens. This allows the widget to display the value including null records. Faceting allows the calculations to adjust when filters are applied.

Risorse aggiuntive
Numero articolo Knowledge

000382902

 
Caricamento
Salesforce Help | Article