Part 2: Using Row-Level Formulas to determine the attainment of multiple goals in a single report
In Part 1, I walked through how to track goal attainment against a single Salesforce field using CASE statements. In this post, Part 2, I'll review how to track goal attainment against several Salesforce fields using IF statements.
IF statements
IF statements are great for when things need to get a bit more complex. Using an IF statement alongside the AND() formula function can add another layer of consideration to the formula you’re calculating. For example, if you need to assign a goal amount based on both the record owner as well as a picklist value selected on the record, then an IF statement would allow you to use both of those record characteristics in the calculation.
A basic IF statement looks like this:
IF( logical_test , value_if_true , value_if_false )
You are also able to create nested IF statements to consider multiple criteria for different results. A nested IF statement removes the 'value_if_false' portion of the statement and replaces it with another IF statement to consider until the last IF statement.
IF( logical_test , value_if_true ,
IF( logical_test , value_if_true ,
IF( logical_test , value_if_true , value_if_false )))
The most important rule when using IF statements is to understand that once a record meets the qualifications of one IF statement, it won't be considered for any other IF statements that come after.
Example: Tracking Revenue Goals using a ‘Territory’ Picklist Value and the Fiscal Period
In this example, I will be building a report that tracks the attainment of four different territories against their goal amounts for two different fiscal periods.
I’ll be using the same territories as in Part 1 but I will be adding another goal amount for each of the territories to represent two different fiscal periods — Q2 of 2023 and Q3 of 2023.
(If you’re new to row-level formulas, I recommend skipping back to Part 1 for a more straightforward formula to start!)
1. Getting the Report Ready
The first thing I’ll 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. I’ll also add a secondary field grouping for ‘Fiscal Period’ using the standard Salesforce field.
For the report I’m building, I need to filter to only show Closed Won opportunities since I want to track the amount we’ve won against the goal amounts. I also need to filter to only show the fiscal periods I’m interested in — in this case, we’re only interested in Q2 and Q3 of 2023 so our Fiscal Period filter should look like the following:
If there are additional territories in Salesforce that we don’t want to report on at this time, we should filter those out as well to ensure a focused report.
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 “Quarterly Territory Goal”. 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 IF function. To reiterate, this formula is structured as follows:
IF( logical_test , value_if_true , value_if_false )
I’ll need to use a nested IF function for this formula since I’ll need to represent each iteration of Fiscal Period and Territory for the goal amounts. This will eventually result in 8 different IF Statements.
I am then going to use the “Fields” tab to search for the territory field I would like my formula to reference. Unfortunately, ‘Fiscal Period’ is less straightforward to add to my formula since it is more of a reporting functionality and less of an actual Salesforce field. One way to get around this is by using the MONTH() Function around the Close Date field, CLOSE_DATE, to get the numeric month value.
Here is the formula for our Q2–2023 goal amount for the “North” Territory:
In the logical_test portion of my IF Statement, I have done the following:
Inserted the AND() function surrounding all logical_test criteria so it will all be considered.
Added the Territory field (Opportunity.Territory__c) = “North” to apply this goal amount to only the Opportunities in the “North” Territory. I have also placed the TEXT() function around the Territory field since we only want to use the text contained in the picklist field.
Inserted the OR() function around three MONTH(CLOSE_DATE) criteria separated by commas since our Q2 is during April, May, and June which can numerically be represented as 4, 5, and 6.
Added “200000” to the value_if_true section of the IF Statement since that is the Q2–2023 goal amount for the “North” Territory.
Removed the value_if_false section and end parentheses for now since I will be nesting multiple IF Statements.
To calculate both the Q2 and Q3 goal amounts for the “North” Territory, I’ll need to copy the first IF Statement and paste it below before making a couple of changes. I will need to update the MONTH(CLOSE_DATE) numbers to represent our Q3 months (July, August, September = 7,8,9) and update the value_if_true section to show the Q3 goal amount, $220,000.
Finally, I am going to copy those two IF Statements lines three more times until I have a total of 8 IF Statements. I’ll need to update the Territory value on each pair of IF Statements and the goal amount on each IF Statement (if it varies by Territory and Quarter).
I’ll also need to add the value_if_false back in with an end parentheses for each IF Statement (in my case, that will be 8 end parentheses).
This will be the final version of the “Quarterly Territory Goal” Row Level Formula:
After I click the “Apply” button and run the report, my result will show the associated goal amount on each row for the proper Territory and Fiscal Period grouping.
To calculate the attainment of each Quarterly Territory Goal, I’ll need to complete one final step.
3. Calculate Attainment Percentage with a Summary Formula
To compare the amount of won bookings to the goal amount for each territory and quarter, 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, “Quarterly Territory Goal”, 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, “Quarterly Territory Goal” (displayed in the formula builder as “CDF1:SUM”), by RowCount.
For example, I know the Q2 goal amount for the “North” territory is $200,000. And it looks like our won bookings in the“Amount” subtotal currently equals $235,000. But if I were to build a Summary Formula for attainment percentage that calculated the Amount/Quarterly Territory Goal, I would end up with $200,000/$235,000 = 85%. That is why I need to divide the “Q1 Goal Assignment” by the number of opportunities (rows) to get the actual goal amount, $200,000.
That is how I ended up with this formula:
AMOUNT:SUM/(CDF1:SUM/RowCount)
*Remember — CDF1:SUM is equal to the sum of the “Quarterly Territory Goal” Row-Level Formula
For a cleaner look, don't forget to go adjust the Display settings in the Summary-Level Formula. For my report, I only want to show the "% to Goal" results for each fiscal period.
Once the Summary-Level Formula has been applied, the report should look like this:
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 “Quarterly Territory Goal” field as a sum (CDF1:SUM) and divide it by the RowCount.
This will make my report much easier to understand by displaying the goal that we are trying to achieve in each territory as shown below. This column can then also be used in reporting.
And I’m done! Here is the quick chart I added to visualize our attainment using “% to Goal” as the measure and a reference line at 100:
