Need help with a summary level formula

Question

first time posting here and looking for some help. I am currently doing a chargeback report in which I need to show a total current share. I use a series of summary formulas to create the needed fields that our customer needs for their quarterly chargebacks.

The issue is, all my fields are totaling their grand totals properly, but this current field is just showing zero. We use all custom objects in our orgs so the formula I post here will be a generic named version to hide our custom field names.

IF( 
  AND( 
    deductible:sum > paid:sum - amount:sum,  
    deductible:sum  <= paid:sum
  ),  
  deductible:sum - (paid:sum - amount:sum),
  IF(
    paid:sum - amount:sum <= deductible:sum, 
    amount:sum, 
    0
  )
)

So basically this is saying if the deductible is greater than the paid minus the amount, AND the deductible is less than or equal to paid, then show the deductible minus the difference of paid minus amount. If it doesn’t meet these requirements, paid minus amount is less than or equal to deductible, show the amount, if not, return 0.

The issue is, it runs for each claim and does the calculations correctly, but the grand total is equal to zero. If I change the zero in the formula to any number, it shows that number in the grand total. I am guessing something is wrong with the formula, but I cannot figure out what.

Answer 1

Because you're summing, the formula won't work per claim, but per grouping. In other words, you're going to get the wrong value. Instead, create a new Formula field on the object, then add that field to your report; it will turn out the correct values then.