I have a report that gives averages of projected work orders compared to completed work orders for each of our departments, individually. This same report gives the averages for all of the departments as a whole. I would like to average the averages for all of the individual departments. For example,
1094/ 1123 = 97%
435/ 444 = 98%
126/ 134 = 94%
66/ 66 = 100%
216/ 242 = 89%
99/ 99 = 100%
354/ 368 = 96%
191/ 194 = 98%
36/ 41 = 88%
112/ 132 = 85%
The summary average is 2729/ 2843 = 96% which is different than the average of 97%, 98%, 94%, 100%, 89%, 100%, 96%, 98%, 88%, 85% which is 95%
can't you simply create a calculated item for it?
such as:
total(completed orders for report)/total(projected orders for report)
I think that should work fine.
I'm afraid not, the "total(completed orders for report)/total(projected orders for report)" is what I meant by "the summary average is 2729/ 2843 = 96% which is different than the average of 97%, 98%, 94%, 100%, 89%, 100%, 96%, 98%, 88%, 85% which is 95%".
But the calculation of total(completed orders for report)/total(projected orders for report) gives you the ACTUAL average for all departments. The average of the averages is not very worthwhile since they are not weighted based on activity.
Here is an example, very simplified to push across my point.
You have 2 departments...
Dept-1: 100/100 = 100%
Dept-2: 1/2 = 50%
Possible average calcs:
-----------------------------------------------------------------------------
Total method: 101/102 = 99%
Avg of Avg's method: 100 + 50 / 2 = 75%
Of these 2 possiblilities of an Average, what value is accurate and realistically worth anything?
I know what you are saying, the average of the averages is not very worthwhile since they are not weighted based on activity. I was afraid that I would get that response. Regardless, is there a way to make an average of the averages? Thank you.
Hmmm...
I'd first of all take the total of each % values such as for 97% it will be .97, for 98% it'll be .98 etc...
Then divide the total number by Count(departments or whatever is your criteria) to get the final number that can be formatted as %.
----------------
I'm afraid not, the "total(completed orders for report)/total(projected orders for report)" is what I meant by "the summary average is 2729/ 2843 = 96% which is different than the average of 97%, 98%, 94%, 100%, 89%, 100%, 96%, 98%, 88%, 85% which is 95%".
Thank you for your response. But what if a department is 0%? I would not want to divide 10 departments by 10 if only 9 departments have averages greater than 0%? Even if your suggestion would work would you then reference the percentage field and then reformat the new field as a decimal? Would your average calculation look like this? count(average of departments/# of departments)?
In that scenario, you have to create a data item that counts # of departments only if avg > 0.