My requirement is I have a custom picklist field in the Contact object name stage, like MQL(Mkt qualified Leads and DQ(Disqualified leads) SQL(Sales Qualified Leads) Now I would like to calculate and represent it in a report that the conversion rate of DQ over MQL and SQL over MQL how to calculate in salesforce report. For simple, If DQs are 10 and MQLs are 100 then the conversion rate should be 10% but I would like to calculate it in the report please advise.
1
-
You can group the rows by the picklist field, and you will get the total counts per the picklist value. But you won't get anywhere further as salesforce reports does not allow having cross groups formulas, just row ones. Maybe there will be a way to display your intent with some plot?– DamecekCommented Sep 6, 2022 at 11:43
Add a comment
|
1 Answer
Depending on your exact requirement, you can probably do this using Summary Formula Columns (https://help.salesforce.com/s/articleView?id=sf.reports_formulas_parentgroupval_prevgroupval.htm&type=5).
- group the rows by the picklist field
- click on the dropdown next to 'Columns' and select 'Add Summary Formula'
- fill in the formula details:
Don't forget to also go to the 'Display' tab and select Row Group as the picklist.
If your picklist values are in the right order, the formula will show relative changes from picklist value to picklist value.
-
Thanks, Guy Clairbois it is very close to resolving my issue now the problem is it is calculating % to its peer stage value whereas I need it to be calculated on the total count, I am trying to apply PARENTGROUPVAL(RowCount, Contact.Stage__c) but it shows Incorrect number of parameters for function 'PARENTGROUPVAL()'. Expected 3, received 2 error could you please tell me how to overcome this problem. Commented Sep 6, 2022 at 15:35
-
this worked for me: RowCount / parentgroupval(RowCount,GRAND_SUMMARY) Commented Sep 7, 2022 at 8:55