In the Report Center, you can build a Custom Analysis that will tell you how much commission your Sales Reps have earned based on the projected revenue from Estimates. In order for this analysis to work, you will need to be using Estimates in SA, have a Sales Rep assigned to the Estimates on which you'd like to track commission, and a consistent use of statuses on your Estimates.
The Case Function allows you to apply different commission rates for different Sales Reps. This way, you could have a single table to track commission for all Sales Reps. If all your Sales Reps or the ones you want to track are making the same commission rate, see this article for a simpler process.
Create the Data Set
Before you can begin using Formulas, you will first need to build a data set for your new analysis. For the purpose of this commission analysis, here are the minimum recommended data points:
The data points selected are Estimate Date, Estimate Description, Estimate Stage, Projected Revenue, Sales Rep, and Client Name. You can add any additional data points you would like. Additionally, you may want to re-order the columns, filter by Estimate Stage, and Group the Sales Rep column as shown below:
Add the Case Function Formula
To add the Formula, use the following steps:
- In your analysis, click the "Formula" tab.
- Give your Formula a Name such as "Commission Amount".
- From the Formula dropdown list, select the "Case When Expression". This will add it to the Formula field in the necessary form for the calculation to function.
- Click in the Formula field after the word "Case" to leave your cursor in that location.
- From the Insert a Column selection list, select "Sales Rep".
- In the Formula field, remove the word "Expression" and replace it with the name of a Sales Rep, exactly as it appears in the table, with single quotes around the name.
- In the Formula field, click after the word "Then" to leave your cursor there.
- From the Insert a Column dropdown list, select "Projected Revenue".
- Immediately after the close bracket, either type an asterisk or select "Multiplication" from the Operator dropdown list.
- Enter the commission rate as a decimal for the Sales Rep whose name you added to the Formula. If you pay 3% commission, you would type ".03". This will multiply the Projected Revenue column by the commission rate in a new column. At this point, your Formula will look similar to this:
- If you need to add additional Sales Reps and commission rates, copy from the word "When" through the end of the commission rate and past it before the word "True". This screenshot shows what you should copy:
- In the copied text, change the name of the Sales Rep and commission rate. Your Formula would look something like this:
- To add additional Sales Reps and commission rates, repeat steps 11-12.
- From the Formula field, delete the text "True Else False". Your completed Formula will look something like this:
- Set the Data Type to "Number".
- Set the Display Format to $#,##0.00 for a dollar value.
- Click Add. This will add a new column to your table with the name of the Formula as the column header.
If you make a mistake in the Formula, the reason the Formula didn't calculate will be displayed. Sometimes these messages are difficult to decipher. Refer back to the screenshots from this article to make sure you did not miss any steps. Once your Formula is fixed, click Replace.
Once you have added the Formula, you can Aggregate the new column for a sum to tell you the total commission amount. If your table is grouped by Sales Rep there will be a total for each Sales Rep as well if you aggregate the new column.