Tracking Attainment in Salesforce Reports with Row-Level Formulas (Part 1)

6 min readJan 6, 2021

Row-Level Formulas are the key ingredient in determining the attainment of multiple goals in a single report

One of the most impactful features available when building a Salesforce report is the ability to add a Row-Level Formula. A Row-Level Formula allows you to add another column to your report that can be populated with a text or numeric value for each record within your report. This differs from the Summary Formula which only allows you to calculate a numeric value for multiple rows.

In this post, I’m going to be walking through how Row-Level Formulas can be used to enable Sales Teams to track their progress against several goal amounts in a single Salesforce report. The formula type I use most often for attainment calculations is the CASE statement.

CASE statements

Case statements work well when you are attempting to assign a value using a single record characteristic. For example, CASE statements are ideal if you want to assign a goal amount:

Based on the owner of the record
Based on a picklist value selected on a record
Based on a formula field result on a record

CASE statements work when the field you are using in the calculation has a predictable result rather than something that varies.

Example: Tracking Revenue Goals using a ‘Territory’ Picklist Value

In this example, I will be building a report that tracks the attainment of four different territories against their goal amounts.

Here are the goal amounts I’ll be using:

Getting the Report Ready

The first thing I’m going to do is add all relevant fields to the Salesforce report and group the report rows by the ‘Territory’ field I’m using for the calculation.

For the report I’m building, I need to filter to only show Closed Won opportunities since I want to track the opportunities we’ve won against the goal amounts. I’m also going to filter the report to only show territories that I want to show attainment progress for. Having extra territories in the report will only clutter my results.

2. Create a Row-Level Formula

The ‘Add Row-Level Formula’ option can be found in the dropdown next to the Columns section of the report builder.

To make it clear which formula is assigning the goal amounts, I am going to name the Row-Level Formula “Q1 Goal Assignment”. I am going to set the Formula Output Type as Number.

First, using the “Functions” tab on the left, I’m going to insert the CASE function. I am then going to use the “Fields” tab to search for the territory field I would like my formula to reference.

After I have added the territory field, I am going to list the names of the territories I would like to track attainment for followed by their goal amounts. The territory names must be surrounded by quotation marks and look exactly the same as they appear in the field.

I then need to add the word null to the end of the formula to account for any records with territories that we aren’t assigning a goal amount for. We should have filtered out all of those records already, but this is just part of the function format.

The report preview will now look like this:

As you can see, each opportunity record has now been assigned the full territory goal amount in the “Q1 Goal Assignment” column. As an example, for the “North” territory, the “Q1 Goal Assignment” subtotal is $60,000 even though the goal amount for that territory is only $30,000. I will need to remember to fix this in the next step so I calculate the correct attaintment.

3. Calculate Attainment Percentage with a Summary Formula

To compare the amount of won bookings to the goal amount for each territory, I’ll need to create a Summary Formula. “Add Summary Formula” can be found in the same dropdown next to the Columns section of the report builder. You must have at least one field in the “Group Rows” section to add a Summary Formula.

I am going to name this Summary Formula “% to Goal” and set the Formula Output Type as Percent. Since I use the “Amount” currency field to calculate the amount of won bookings at my organization, I am then going to use the “Fields” tab to insert the “Amount” field as a sum.

Next, I need to divide the amount of won bookings by the related territory goal amount. I am going to add some parentheses and then use the “Fields” tab to insert the Row-Level Formula I have just created, “Q1 Goal Assignment”, as a sum. No matter what you name your Row-Level Formula, it will always show as “CDF1” once inserted into your formula builder.

This is where it gets a little tricky. Because I previously assigned the full goal amount of each territory to the related opportunity record, I will need to divide the Row-Level Formula, “Q1 Goal Assignment” (displayed in the formula builder as “CDF1:SUM”), by RowCount.

For example, I know the goal amount for the “North” territory is $30,000. And it looks like our won bookings in the“Amount” subtotal currently equals $30,000. But if I were to build a Summary Formula for attainment percentage that calculated Amount/Q1 Goal Assignment, I would end up with $30,000/$60,000 = 50%. That is why I need to divide the “Q1 Goal Assignment” by the number of opportunities (rows) to get the actual goal amount, $30,000.

That is how I end up with this formula:

AMOUNT:SUM/(CDF1:SUM/RowCount)

*Remember — CDF1:SUM is equal to the sum of the “Q1 Goal Assignment” Row-Level Formula

4. OPTIONAL: Show Goal Amounts with a Summary Formula

To add more clarity to my report, I’m also going to add an additional Summary Formula that is equivalent to the portion within parentheses of the Summary Formula I just built for the attainment percentage.

I am going to name this Summary Formula “Goal Amount” and set the Formula Output Type as Currency. I am then going to use the “Fields” tab to insert the “Q1 Goal Assignment” field as a sum (CDF1:SUM) and divide it by the RowCount.