Loading

How to Display Cumulative Aggregated Record Counts at Each Grouping Level in a Salesforce Summary Report.

Julkaisupäivä: May 5, 2026
Kuvaus

When creating a Salesforce Summary Report grouped by a picklist field, the standard record count shows the count for each individual grouping value. To display a cumulative running total that accumulates across groups, a combination of a bucket column, a row-level formula using the CASE function, and a summary formula using PREVGROUPVAL is required.

Ratkaisu

The below is specific to the lead status, but this can apply to any report grouping. However, it works best with single picklist or checkboxes as there are set values that work well with formulas.

Also, if you have more or less than 4 values in your grouping, change the formulas accordingly. 

Step 1: We need to list the Statuses in order. By default, Salesforce sorts them in alphabetical order. The easiest way to do it is to create a bucket column (see image below). 



Step 2: Create as many buckets as you need for your statuses, but name them as shown below, with a number in front of their names:
 

1- Open-Not Contacted
2- Working-Contacted
3- Closed-Converted
4- Closed-Not Converted
 



Step 3: Click on Unbucketed Values bucket, select the first status, click Move to, and select "1-Open-Not Contacted." Repeat for each value and then click Apply. 



Step 4: 

  1. Now, group your report by this bucket.
  2. If the highest value needs to be the one with the smallest count, click on the Bucket column in the report so the order is inverted (the top one will be "4-Closed-Not Converted"); otherwise, leave as-is. 

Step 5: Create a Row-Level Formula by clicking the down arrow in the Columns section and selecting Add Row-Level Formula. 

Add Row-Level Formula.png

Note: 
  • Give it a name and make sure it returns a number without any decimal points. 
  • Enter the below formula. 
Tips: 
  1. If you will reuse this formula in other reports, it would be better to create formula field on the lead object. 
  2. If using a Row-Level Formula, select the STATUS from the left-hand-side panel to get the right spelling. 
CASE(STATUS, "Open - Not Contacted",1, "Working - Contacted",2, "Closed - Converted",3,4)
 


Note: As an example, if you have 5 values, the formula would be:  
CASE(STATUS, "Open - Not Contacted",1,
"Working - Contacted",2,
"Closed - Converted",3,
“Value4”,4,5)
Step 6: Create a Summary Formula by clicking the down arrow in the Columns section and selecting Add Summary Formula. 

Add Summary Formula.png

Step 7: Enter the following formula, returning a number with no decimal points.  
RowCount+
CASE(CDF1:AVG,
4,0,
3,BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_60270980),0),
2,BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_60270980,2),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_60270980),0),
BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_60270980,3),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_60270980,2),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_60270980),0))


Tips: 
  1. Select the necessary fields from the left-hand-side panel to get the right spelling and "Average" in the dropdown. If using a Row-Level Formula, it will show as CDF1:AVG in the formula box. 
  2. Your bucket name will be different to the one in this formula. You will find it on the left hand side panel in the formula by scrolling all the way down and selecting it from the Grouping Fields section. 
  3. As an example, if you have 5 values, the formula would be: 
RowCount+
CASE(CDF1:AVG,5,
0,
4,BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257),0),
3,BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257,2),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257),0),
2,BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257,3),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257,2),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257),0),
BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257,4),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257,3),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257,2),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257),0))

Step 8: While in the formula window, click the Display tab, select the Specific Groups level, select your bucket column grouping, and click Apply. 



And voila!!!

Here's an example of what your report should look like (with details hidden) with the Row-Level Formula (with averages) shown in green, the record count for each specific status in blue, and the aggregated record count in red: 

Report Result.png
 
Knowledge-artikkelin numero

000393469

 
Ladataan
Salesforce Help | Article