Get a percentage on a subset of picklist items - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Michael FaberMichael Faber 

Get a percentage on a subset of picklist items

I am trying to write a report formula that will return the % of one option from a picklist.

Lets say we have 10 items returned in an activity report, and we are grouping by Type (picklist) and Sub Type (picklist). The Sub Type picklist contains options Inquiry and Transfer

We are trying to create a column that will return the percentage of rows that have selected Sub Type Transfer, irrespective of Type.

User-added image

Is this possible without configuration changes?
Best Answer chosen by Michael Faber
Steve MolisSteve Molis
These are the Formulas that I used (keep in mind I uswed an Opportunity Report in my example to approximate the data)

Formula A
IF(PARENTGROUPVAL(RowCount , CREATED_DATE, COLUMN_GRAND_SUMMARY) = 0, 0 ,
RowCount
 / 
PARENTGROUPVAL(RowCount , CREATED_DATE, COLUMN_GRAND_SUMMARY)
)

Formula B
IF(PARENTGROUPVAL(RowCount, TYPE, COLUMN_GRAND_SUMMARY) = 0, 0,
RowCount / 
PARENTGROUPVAL(RowCount, TYPE, COLUMN_GRAND_SUMMARY))
Formula C 
IF( PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, Opportunity.Do_you_owe_SteveMo_a_beer__c) = 0, 0, 
RowCount / 
PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, Opportunity.Do_you_owe_SteveMo_a_beer__c))


​​​​​​​

All Answers

Steve MolisSteve Molis
So using the numbers in your Report screenshot, what numbers are you expecting this formula to return?
Michael FaberMichael Faber
We are hoping to get the percentage of the total number of records that were Transfers. In this case, 30% as 6 of the 20 were transfers.
Mike BrownMike Brown
Hi Michael, 

This might be easier if you move the Activity Type grouping to the Row Level. That would then let you see the total amount of Sub-Types across all Activity Types. 

For example, here's a report where I have my Opportunity rows grouped by Type, and then the columsn are grouped by Stage. I can then see the totals for each Stage, regardless of what type it is. 
User-added image

Thanks,
Mikey
Steve MolisSteve Molis
Are you trying to do something like this (Formula B)
IF(
PARENTGROUPVAL(RowCount , CREATED_DATE, COLUMN_GRAND_SUMMARY) = 0,
 0 ,
RowCount
 / 
PARENTGROUPVAL(RowCount , CREATED_DATE, COLUMN_GRAND_SUMMARY)
)
User-added image
 
Michael FaberMichael Faber
Oh interesting! Let me try that.
Michael FaberMichael Faber
Not quite as the requster is trying to get only the Transfer percentage total in a separate column, or at least by itself. They really want the sub totals broken out by activity type, and then the sub types percnetages also sub totalled but not by actviity type. Something like this very badly formed knockup.

User-added image
Steve MolisSteve Molis
They way they have the Report setup now is a real wrinkle.  Does it absolutely HAVE to be setup the way that they have it?
Michael FaberMichael Faber
They do like the setup, but I am happy to provide options if it gets them the value they need. the grouping by month is key however.
Steve MolisSteve Molis
Would something like this work? 

 
User-added image
Michael FaberMichael Faber
Quite possibly! What would the formulas be?  We are still in classic btw. (way behind the curve I know. we are working on it)
 
Steve MolisSteve Molis
These are the Formulas that I used (keep in mind I uswed an Opportunity Report in my example to approximate the data)

Formula A
IF(PARENTGROUPVAL(RowCount , CREATED_DATE, COLUMN_GRAND_SUMMARY) = 0, 0 ,
RowCount
 / 
PARENTGROUPVAL(RowCount , CREATED_DATE, COLUMN_GRAND_SUMMARY)
)

Formula B
IF(PARENTGROUPVAL(RowCount, TYPE, COLUMN_GRAND_SUMMARY) = 0, 0,
RowCount / 
PARENTGROUPVAL(RowCount, TYPE, COLUMN_GRAND_SUMMARY))
Formula C 
IF( PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, Opportunity.Do_you_owe_SteveMo_a_beer__c) = 0, 0, 
RowCount / 
PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, Opportunity.Do_you_owe_SteveMo_a_beer__c))


​​​​​​​
This was selected as the best answer
Michael FaberMichael Faber
This is excellent! Thank you most kindly. I should we ever meet, I most definitely owe you a beer.
Steve MolisSteve Molis
No problem, Mike, Ill put it on your tab 

Cheers,

SteveMo