If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

How to make an Average of Averages- Thank you

Started by JoelR, 05 Sep 2007 02:36:43 PM

Previous topic - Next topic

JoelR

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%

Suraj

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.

JoelR

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%".

rockytopmark

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?

JoelR

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.

Suraj

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%".

JoelR

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)?

Suraj

In that scenario, you have to create a data item that counts # of departments only if avg > 0.