+ Start a Discussion
b.gonzalezb.gonzalez 

Summary Total Amount on Report Formula?

Hi there,

I created the following report formula called 'Total for Current FQ'. In the report, the opportunity values, should added up to the summary value but that is not the case. See image of the report below.  I appreciate any help on this. Thank you!

IF(OR((
Opportunity.Total_Amount_for_Current_FQ__c:SUM==0.00),
Opportunity.Total_Amount_for_Current_FQ__c:SUM==null), AMOUNT:SUM,
Opportunity.Total_Amount_for_Current_FQ__c:SUM)

User-added image

Here is the report:
User-added image

Beth
 
Best Answer chosen by b.gonzalez
Doria Hamelryk VetranoDoria Hamelryk Vetrano
Hi Beth,

In fact your formula works : it takes the FQ amount over, and if it doesn't exist, it takes the Amount field over.
You see it clearly on the Opportunity level of your report.

You have made this formula visible on the OEM level, but it reacts like defined in the formula, not as a summary (SUM)

So what I mean is that, for each line of your report, wherever you are looking at (OEM level, Opp name level) in the "Total for Current FQ", you will have :
  • the value in "Total amount  for current FQ" if it exists // on the same line
  • the "Amount" value, if "Total amount  for current FQ" =0 // on the same line
And this behaviour is the same for each line, whatever you take as level in your report.

You can not make summarize a formula field - or I never seen how to do this :)

If you really need this in your report, a work-around would be to implement fields on the opportunity that will be updated by triggers/flows

All Answers

Doria Hamelryk VetranoDoria Hamelryk Vetrano
Hi Beth,

In fact your formula works : it takes the FQ amount over, and if it doesn't exist, it takes the Amount field over.
You see it clearly on the Opportunity level of your report.

You have made this formula visible on the OEM level, but it reacts like defined in the formula, not as a summary (SUM)

So what I mean is that, for each line of your report, wherever you are looking at (OEM level, Opp name level) in the "Total for Current FQ", you will have :
  • the value in "Total amount  for current FQ" if it exists // on the same line
  • the "Amount" value, if "Total amount  for current FQ" =0 // on the same line
And this behaviour is the same for each line, whatever you take as level in your report.

You can not make summarize a formula field - or I never seen how to do this :)

If you really need this in your report, a work-around would be to implement fields on the opportunity that will be updated by triggers/flows
This was selected as the best answer
b.gonzalezb.gonzalez
Thanks Doria!

Beth