Basically I am just trying to sum up the probability weighted forecasted Gross Margin and Contribution Margin for each opportunity by fiscal year.
In Opportunities Object, I have related lists of Budget by Fiscal Year and Payment by Fiscal Year.
Note that Budgets are always done by Fiscal Year, but there could by multiple Payments scheduled within any fiscal year.
In the matrix report below, I have formulas which calculate probability weighted GM and CM per fiscal year based on Sum of Payments and Avg of Budget (I did it this way so that I would not double count the budget in cases where there were 2 payments associated with 1 fiscal year budget). For each opporunitiy the calculations are correct.
The problem is in the subtotal in that instead of just summing my calculated GM and CM lines, the subtotal is re-calculating based on the sum of ALL payments and avg of ALL budgets. This error does not let me see the total forecast GM and CM which is what I really need to know if I am on track to hit my numbers within the fiscal year.
Not sure if this can be solved in the report setup or if the record structure needs to be changed. Thanks for your help!
Kevin
답변 1개
Hi Kevin,
I believe the behavior is to be expected since the custom summary formula is calculating per grouping of each cell. I'm not sure you can display the calculated GM and CM Lines into the subtotal (perhaps via Prevgroupval if you can specifically define the number of groupings) either. Perhaps another way try this is to perform the initial calculations on the record level (rollup into the Opportunity, then into the Account).
Thanks!
