My report type is "As with Bs" where A and B are custom objects and B has a numeric field. In the report, I grouped the numeric field for A.
I want to display the sum of this field for each A and in the grand total I want to display the average of the sum.
If I summarise with sum and average I correctly obtain the sum for each A but the average in the grand total is on the entries.
I've created a sample report with the following assumptions:
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:SUMThe sample report is below:
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
Create a custom report type (Setup -> Create -> Report Types).
Call it "Accounts With Timesheets" or something like that, starting object would be Account. Relationship - I think it'd be best to set it to "with or without".
On the next page you'll be selecting fields to include (scroll down to the "Edit layout"). On the right side there will be blue "view" box with option to add fields related via lookup. You can use this to go from "Account Type 1" to it's parent "Account Type 2".
Add as many fields as you need, maybe also create a new section, call it "Parent Account" and move the new fields there (will be easier for users to identify which field they want to use in the report).
Notes:
Best Answer
I solved the issue with the Power of 1:
1.At subtotal, the sum of the new formula field is 1 because Salesforce use the smart sum. At grand total, the sum of the new formula field is the number of groups.