Skip to main content
Topics#Reports & DashboardsDaniel Grenell's Question
Daniel Grenell asked in #Reports & Dashboards
Feb 26, 2021, 4:56 PM

Find % of records with related record

I would like to know what percent of my records on a certain object (Households) have records on a related object (Service) created within a given time period.

 

I've tried using the power of 1 formulas on each of these objects, but I can't seem to get the summaries to work. So far the closest I've come to a working summary formula is

 

IF(Service.Power_of_1__c:SUM<1, 0, 1)/PARENTGROUPVAL(Case_Record.Power_of_1__c:SUM, GRAND_SUMMARY)

 

displayed at the level of the Household. I can export this to excel and sum the column to get the percent of case records with service, but I can't get SalesForce to accurately report this sum on the report, which I would like to be able to use on a dashboard.

 

I've tried googling, but come up short. I appreciate any help. Thanks in advance.
9 answers
  1. Eric Praud (Activ8 Solar Energies) Forum Ambassador
    Mar 2, 2021, 9:22 AM
    Hi Daniel,

     

    I just found a way to do it without triggers.

     

    Create a summary formula like so:

    (RowCount-Service.Power_of_1__c:SUM)/Case_Record.Power_of_1__c:SUM

     

    and display it om the "Grand Total Only". You'll get the one value only, and you can then add it to a Metric chart on a dashboard :)

     

    Here's my formula with Accounts and Opps:

    (RowCount-Opportunity.Count_Opportunity__c:SUM)/Account.Count_Account__c:SUM

     

    And here's the resulting report:

     

    Hi Daniel, I just found a way to do it without triggers. Create a summary formula like so:(RowCount-Service.Power_of_1__c:SUM)/Case_Record.Power_of_1__c:SUM and display it om the

     

    As you can see, I have 8 Accounts without Opportunities out of 17 accounts:

     

    8/17*100=47.06​​​​​​​
Write an answer...
0/9000