Such a requirement calls for the use of the PARENTGROUPVAL reporting function.
Here are the step by step directions on how to build thisvformula using PARTENTGROUPVAL in a Salesforce.com Summary Report.
- Create a summary report, add the necessary fields for Manager, Owner, Status, etc., it should look something like this:
- Next add grouping by Owner and Status:
- Next, add a formula, name it “Conversion Rate”, select “Percent” as the Format and 0 for the number of Decimal Places and select Grouping 2: Status for the location of the formula to be displayed:
- Next you start to build the formula. Click Summary Fields >> Record Count and the word RowCount will be added to the formula builder. This represents the number of rows for the grouping level specified in step #3: >>
- Click Operators >> Divide and “/” will appear next to the word RowCount
- Next, under functions, click Summary, then PARENTGROUPVAL, be sure to select Lead Owner (not Grand Summary) for the grouping level. Then click the insert button:
- Your formula should now read RowCount/PARENTGROUPVAL(summary_field, OWNER).
- The last step is to overwrite the placeholder “summary_field” with RowCount and click OK
That should do it. Your results should look something like this when you run the report:
You can also use this formula in a matrix report, however the syntax is slightly different:
RowCount/PARENTGROUPVAL(RowCount, OWNER, COLUMN_GRAND_SUMMARY)