Hello there,
We have set specific fundraising goals for specific segments of our donor base. I'd like to create a report (and chart) that illustrates how much we have raised to date within each segment and how much is left to raise to meet our goal. The second part has been a bit challenging as I cannot seem to get the row summary for each segment to work.
Since each segment has a unique goal, each row summary needs a specific formula. I'd like to create a formula that calculates the following:
If segment = [SEGMENT NAME], then [GOAL TOTAL] - [TOTAL RAISED BY SEGMENT]
I've tried the following formula. When I validate, it comes back as a valid formula. However, once I press apply I get an error message that my request cannot be completed.
CASE(Opportunity.Segment__c,
'Board', 500000-SUM(AMOUNT),
'Individual', 500000-SUM(AMOUNT),
'Institutional', 500000-SUM(AMOUNT),
'Public', 500000-SUM(AMOUNT),
0
)
Is there an easy way to do this? Can the output be then illustrated in a chart showing amount raised to date and amount left to raise? I've been using a gauge chart to represent progress towards each segment separately, but ideally, I'd like to have this all in one chart.
Appreciate your help!
Zoey
Michael Brown (SETGO Partners) 
Forum Ambassador
This should still be doable. You could add a row level formula that returns the Goal based on that segment, such as this.
CASE(Opportunity.Segment__c,
'Board', 500000,
'Individual', 600000,
'Institutional', 700000,
'Public', 800000,500000
)
Instead of summarizing this in the traditional sense, you could then try calculating the Average of this field into each Segment grouping in your report. This should automtaically happen if you group on segment and select Sum: AVG on your row level summary. This can be a workaround in these types of things.
For example. let's say Board has a goal of 500000, and there are 4 opportunities in this segment
- Opportunity 1: Amount = $100,000, (Row Level Formula = $500,000)
- Opportunity 2: Amount = $50,000, (Row Level Formula = $500,000)
- Opportunity 3: Amount = $50,000, (Row Level Formula = $500,000)
- Opportunity 4: Amount = $100,000, (Row Level Formula = $500,000)
If you summarized the row level formula, that would add up to $2M, but if you take the average, it will return $500,000
You can then modify the custom report summary formula to:
SUM:AVG_Row_Level_Formula - SUM:Amount
Note, that's just an example, the actual formula names will be different.
