+ Start a Discussion
Natalia Rex 1Natalia Rex 1 

Report formula that calculates % based on status vs. total of records

Hello, I want to create a success vs fail percentage of our Assignments, based on a picklist values, called Status. This picklist value Status has up to 8 different statuses.  

For example I want to group Won and Finished records as one group, called "Successes", divide by total assignments to get % of success rate for the assignments.   Then the same thing, for the other statues,call the second group "Fails" to see how many assignments in the success or fails category we have compared to total, to get percentage. To end up getting, 20% success rate, 80% fail rate, out of 250 different assignments created. 

In simple math its basically total of Won + Finished / Total assignments = % success rate then it will be Sent + Lost + any other status / Total assignments = % fail rate.  I have a row level formula that I was able to get to count the "Fails" with this:    
CASE(Assignment__c.Status__c,"Sent",1,0) + CASE(Assignment__c.Status__c,"Lost",1,0)

 And I was able to create a "fails percentage" summary formula, that takes the sum of the fails above and divides it by total of records:   
CDF1:SUM / RowCount
 However, how can I create additional row level formulas so I can see % of "fails", as written above, and % of successes.   I feel that in the row formula I need to somehow GROUP them based on picklist values, that some sort of grouping is needed. And in the summary formula column differentiate it somehow.  
User-added image
Do you have the ability to create a custom field on the Object?  That would make this a lot easier to solve
Natalia Rex 1Natalia Rex 1
Hello, yes I am able to create a custom field on this object, what do you suggest in this case?
Min-Ho SimMin-Ho Sim
please use PARENTGROUPVAL function as below article