You are here:
mpercent
Returns the commission rate for a deal in Salesforce Spiff, based on a range table and the rep's current attainment. To return a commission total amount instead, use the mamount() function.
Required Editions
| Available in: both Salesforce Classic (not available in all orgs) and Lightning Experience |
| Available in: Enterprise, Unlimited, and Developer Editions |
| Available for an additional cost in: Professional Edition with Web Services API Enabled |
Syntax
mpercent(input_value, range_table, initial_value, return_column, return_value_when_input_value_zero)Arguments
| Argument | Required? | Description |
|---|---|---|
| input_value | Required | The percentage or currency amount to check against a range table, such as the percentage of quota retired per deal or an ARR amount from a closed deal. For an amount, the value must match the currency and values in the range table. |
| range_table | Required | The range table to check. |
| initial_value | Optional | A starting value that affects which ranges the input_value applies to. Typically, this value is based on an accumulated variable, which tracks progress toward rate tiers, such as previous progress toward a quarterly quota where commissions are calculated monthly. |
| return_column | Optional | The name of the range table's specific return column, enclosed in double quotes. This argument is required only if the range table includes multiple return columns, which is helpful when the same value ranges are used across multiple plans with different rates. |
| return_value_when_input_value_zero | Optional | A string, number, currency, or calculation that provides error handling or more information when this function can't calculate the input_value. For example, an input_value of 0 results in division by zero, but returns a value of 0 but no error. With this argument, specify a different value or behavior for these cases. |
Considerations
- This function is commission-specific based on a range table and rate tiers.
- This function allocates input_value across tiers by calculating how much falls within each tier range, and then calculates the payout for each tier.
- You can use previously created summary calculations and mathematical operations in a range table. Reference these calculations with the syntax
WorksheetName!CalculationName.
Example
Consider these commission rate tiers in a range table, AttainmentTiers.
- For the first tier, when attainment is between 0% and 50%, a rep receives 5% commission.
- For the second tier, when attainment is between 50% and 100%, a rep receives 7% commission, but that rate applies only to this tier. Deals that closed between 0% and 50% attainment are still commissioned at 5$.
If a rep already has 48% attainment and then closes a new deal that represents 5% of their attainment, only 2% attainment of the new deal is allocated to the first tier at 5% commission. The remaining 3% attainment is allocated to the second tier at 7% commission. The result is a blended rate, based on this calculation.
(2% * 5% + 3% * 7%)/5% = 6.2%To calculate this commission rate, provide these values to the mamount() function.
=mpercent(0.48, AttainmentTiers, 0.05)Did this article solve your issue?
Let us know so we can improve!
