Build Common Calculations

Learning Objectives

After completing this unit, you’ll be able to:

• Build a year-to-date (YTD) commissions paid calculation.
  
• Build a YTD commissions attainment calculation.
  
• Aggregate team member quotas.
  

Build a YTD Commissions Paid Calculation

Often, the question arises: Can you generate a report on the YTD commissions default metric card in custom reports? Unfortunately, no. However, don’t worry! You can build your own calculation that yields the same effect and can be reported on. Additionally, you can use this custom calculation to show the fiscal year YTD Commissions as a metric card.

Here’s how you create a YTD Commission calculation in four steps.

1. Create a YTDCommissionsUpToPreviousPeriod calculation.
   
2. Identify the calculation from each Payout Amount on the plan.
   
3. Create a CurrentPeriodCommission calculation.
   
4. Create the YTDCommission calculation.
   

In this module, we assume you're a Salesforce Spiff administrator with the proper permissions to take these actions. If you’re not an administrator for Salesforce Spiff, that’s OK. Read along to learn how your administrator would take the steps in a production org. Don't try to follow these steps in your Trailhead Playground. Spiff isn’t available in the Trailhead Playground.

Create a YTDCommissionsUpToPreviousPeriod Calculation

You can calculate all of the year except for the current period using the amounts_from() function as a worksheet calculation, using the calculation =amounts_from(beginning_of_year(BeginningOfPeriod),days_ago(BeginningOfPeriod,1), PlanName).

The calculation begins at the start of the year and concludes on the last day of the previous period. This approach is necessary as amounts_from()can't consistently capture the current statement period. ‌The third argument uses the helper function PlanName to ensure the versatility of this calculation across multiple plans. However, you can also hard-code this argument for a single plan by placing the plan name in parentheses, such as (“Sales Rep Plan”).

Identify the Calculation from Each Payout Amount on the Plan

With the YTDCommissionUpToPreviousPeriod calculation, you obtained the total for every period except for the current one. To calculate the amount for the current statement period, retrieve the calculation name of each payout rule associated with the plan.

1. Navigate to the plan page, where you find all the payout rules used on the selected plan.
   
2. Hover over each active rule to identify the calculation name from which it originates. For this step, disregard any payout rules that are unavailable and have an icon next to the payout rule name. For example, ghost rules or what-if rules.
   

Create a CurrentPeriodCommission Calculation

After collecting all the payout amounts from the plan, create a CurrentPeriodCommission worksheet calculation. This calculation sums up all the gathered payout amounts.

Create a YTDCommission Calculation

As the final step to create a YTD Commissions Paid calculation, create a final worksheet calculation that combines the YTDCommissionsUpToPreviousPeriod and CurrentPeriodCommission calculations. This generates the YTDCommissions calculation.

Your worksheet calculation now aligns with the original YTD Commissions Metric mentioned earlier. This confirms that you have the correct YTD Commissions amount, now available in a calculation format suitable for use in reports.

Create a YTD Attainment Calculation

Show your reps their progress toward the attainment of their yearly quota to motivate them to achieve higher commission rates or bonus payouts for different attainment levels. Use these three steps to create a YTDAttainment calculation that shows up as a metric card on the rep’s statement.

1. Create a YTDARR calculation.
   
2. Create an AnnualQuota calculation.
   
3. Create a YTDAttainment calculation.
   

Next, explore each step.

Create a YTDARR Calculation

This calculation sums the total amount of sales or deals that the rep has for the year, up to the current date. ‌This example pulls the ARR field, but in your payout rules you can have a different amount field that you use, such as =sum(ClosedInYear_AESaas!ARR. ‌Make sure to use whatever field you need for your payout rule.

Create an AnnualQuota Calculation

This calculation sums the total amount of the rep’s annual quota. ‌If you have a single, annual quota on a quota table, use the quota() formula as =quota(“AnnualQuota”). If you have set up a monthly or quarterly quota table, calculate the aggregate total for the year.

To sum a quarterly or monthly quota, use the formula =quota("QuarterlyQuota", beginning_of_year(BeginningOfPeriod), end_of_year(EndOfPeriod))

Create a YTDAttainment Calculation

To obtain the YTD Attainment percentage, divide the YTDARR by the AnnualQuota using the formula =YTDARR/ AnnualQuota.

Congratulations! You just learned how to create a YTD attainment calculation that shows up as a metric on the rep’s statement. You can also use this calculation in reports.

Sum Up Team Member Quotas

In many payout rules, it’s necessary to create a calculation that adds up multiple quotas for users on the manager’s team. For example, the manager’s quota is an aggregate of all the team members’ quotas. You can create this calculation with different worksheet calculations in Designer. ‌However, it only works when the manager has just one team. Check out these examples.

You can adjust these calculations to incorporate different time ranges in your individual payout rules. Here's one way to lay out these calculations in a worksheet. The column with names is optional, but a recommended way to organize fields in worksheets. In this example image, the Display Field Names is on.

You can see the trace view if you click SumTeamQuotas and then .

Wrap Up

In this module, you learned about the function of worksheets in Salesforce Spiff—create calculations that produce a single output. You also learned how to create calendar date formulas and other common worksheet calculations in payout rules, such as YTD Commissions and YTD Attainment. You can use these calculations to track sales performance and quotas, among other metrics. These calculations make it easier to incorporate different dates or time ranges into your commission payout rules.

Good luck as you get started with your own calculations!