Is there a way to have a summary report calculate the variance of the difference between sums?

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • Answered
Basically I need the variance to be calculated as the following:

FTE Target - FTE Sum (Filled) - FTE Sum(Open) = Variance. In which case would be 
9.6 below.


Photo of Joe Baez

Joe Baez

  • 204 Points 100 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
The only way to do this would be to have a table of Cost Centers (which you may already have) and summarize up the detail records for Filled and Open.

Then just have s simple Table Report of Cost Centers and do the math on the Cost Center Record.
Photo of Joe Baez

Joe Baez

  • 204 Points 100 badge 2x thumb
Hi Dan,

Correct I do have Cost Center table. I'm also am doing the math in the record but it sums the individual variances on the summary which is not the result needed.

Question - is there a way to create a formula field that captures the totals shown at the bottom of a Report Link?

i.e. pull in the total 40.3 into a separate formula field? I'm thinking as a workaround have the math show in each record..I think this will be acceptable because the report consumer has to view each record individually anyway.



(Edited)
Hi this is Mark.
I'm not understanding the question now.

If you have all your fields on a record, you can just do a simple formula to calculate the variance, right?
Photo of Joe Baez

Joe Baez

  • 204 Points 100 badge 2x thumb
Sorry..Mark.

The variance calc in the record is missing the Filled and Opened totals for the Cost Center. Keep in mind its a sum of multiple records for each cost center. In other words, each cost center has multiple records within it...I need the sum of Total FTE (Open) and (Filled)...if I can get this in the record I'd be in business...the variance calc would work.
If you have a relationship between the Cost Centers and their detail records, then you can make summary fields to get those numbers (Totals or averages) up to the Cost Center record. 
Photo of Joe Baez

Joe Baez

  • 204 Points 100 badge 2x thumb
indeed you can!! woohoo!

awesome bud..thank you!
Sometimes the answers are easy. Glad to hear it’s working.