1

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!

|improve this question
  • How is this configured? Are Loans a master-detail to something like Accounts? If you can give us a better idea of the setup we'll be able to provide a good answer. – Mike Chale Sep 25 '12 at 13:55
  • Which dashboard component were you hoping to use? It sounds like a gauge might be the best, if you're just looking for an overall percentage. – Mike Chale Sep 25 '12 at 14:14
  • Hey Mike, I am planning to use the gauge. Your assumptions below were spot on, thanks! – jackerman09 Sep 25 '12 at 14:14
2

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

  1. Loan is a custom object
  2. 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:

  1. Loan__c.Amount_Outstanding__c:SUM / Loan__c.Total_Credit_Line__c:SUM
  2. Grouped at the grand summary level
  3. 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

|improve this answer
  • Thanks, this is exactly what I am aiming for! Both of your assumptions are correct, and I set up the report and custom formula in the same manner, however, it will not let me drag the formula onto the report. My only thought is that it might be affected by the fact that I only have one loan in the system right now. Is this possible? Thanks! You've definitely nailed down my goals, I just need to get the last little piece in place. – jackerman09 Sep 25 '12 at 14:13
  • I added another loan as a test, but it did not allow me to drag the formula field onto the report. – jackerman09 Sep 25 '12 at 14:17
  • There is nothing to drag; when you double-click "Add Formula" it automatically adds the column for you, though you should see it in the action panel. If you can't drag it over then it is probably already in the report. Try adding a few more records and make sure your groupings are correct. – Mike Chale Sep 25 '12 at 14:18
  • 1
    Try this link - success.salesforce.com/questionDetail?qId=a1X30000000HkQGEA0 – Mike Chale Sep 25 '12 at 14:19
  • That's it! Thanks so much for the help! Marking as answered now. You might wanna add that to your original answer in case anyone else stumbles across this thread. – jackerman09 Sep 25 '12 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.