[SalesForce] How to show (on a dashboard) what % one column total on the report is of another column total on the report

In this situation, I am showing a report of outstanding loans. Each loan has an 'Amount Outstanding' and a 'Total Credit Line' field. Each loan also has a 'Amount Outstanding as a % of Total Credit Line' field.

I would like to display the 'Total Amount Outstanding of All Loans as a % of Total Credit Line of All Loans', however, just showing an average of the individual % fields would not be accurate because it is not a weighted average. How can I show this weighted average on a report (to then display on a dashboard)?

EDIT: Ok, it looks like I can add a Formula Field to a summary report that will calculate the :SUM of each of the columns, then divide one by the other. This is working, however when I click 'OK' after defining the formula, it is not getting added to the report preview, and it won't let me drag it on. Any idea why this is happening?

Thanks!

Best Answer

I've created a sample report with the following assumptions:

Loan is a custom object
Amount Outstanding and Total Credit Line are fields on the Loan object

I then created a summary report, grouped by Loan Name, and added formula field, "Total Percent Outstanding", column defined as follows:

Loan__c.Amount_Outstanding__c:SUM / Loan__c.Total_Credit_Line__c:SUM
Grouped at the grand summary level
Format is percent

The sample report is below: enter image description here

The total amount outstanding here is $129 out of $1,300 in credit, for a rate of 9.92% outstanding.

Edit: I just noticed you are asking for a dashboard. Which component are you hoping to use?

Update: The drag 'n drop problem was related to grouping, as discussed at http://success.salesforce.com/questionDetail?qId=a1X30000000HkQGEA0

Related Solutions

[SalesForce] How to create a Summary Report, without repeating the same non-grouped column data

I'm afraid it will look very ugly in a summary report. While you can have up to 3 groups in it (Name + Email + Id = cool), it will be displayed as nested groups.

What you can therefore do is to create a formula field on contact (type: text) that will have FirstName + ';' + LastName + ';' + 'Email' + ';' + Id and then group by it in the report. Combine it with 'hide details" (as pointed out by @Saariko) and you're getting somewhere... Still far from ideal I'm afraid.

You should be able to do it in SOQL easily enough, maybe this would be a valid option? I have no idea how your Contacts are linked to Opportunities/Donations so I'll go through standard link. You should manage to adapt it if you have Contact__c on Opportunity or something like that.

SELECT Contact.Name, Contact.Email, ContactId, SUM(Opportunity.Amount) sum
FROM OpportunityContactRole
GROUP BY Contact.Name, Contact.Email, ContactId
LIMIT 10

(As with each aggregated query, if your organisation uses multiple currencies, these results are guaranteed to be in your corporate currency)

[SalesForce] PARENTGROUPVAL to calculate Amount Percentage in Reports

I figured it out. I used the below formula:

(Opportunity.My_Revenue__c:SUM)/PARENTGROUPVAL(Opportunity.My_Revenue__c:SUM,GRAND_SUMMARY)

Also, I applied this to the 'Specified Groups' Stage. And BOOM, I got the exact results.

Hope this helps.

Best Answer

Related Solutions

[SalesForce] How to create a Summary Report, without repeating the same non-grouped column data

[SalesForce] PARENTGROUPVAL to calculate Amount Percentage in Reports

Related Topic