nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Crosstab Taking an Average of Averages

spaddock

I'm trying to create a survey report which needs to take an average of a group of average scores. The problem is BIRT is calculating the overall average using all of the underlying data instead of taking the average of the 3 already computed averages, is there any way to avoid this?
 
As an example, a group of questions could be:
 
Scale 1-100
 
How did you like these things about your school:
 Teacher - 81.9
 Library - 92.9
 Playground - 99.3
 
I want the overall score to be 91.37
(81.9 + 92.9 + 99.3) / 3
 
But I'm getting a slightly lower number like 91.25 because BIRT isn't taking the average of the 3 averaged numbers but the average of all the scores in the group.
 
I hope I have explained this well enough.
 
 
Kindest regards,
Shayne

Find more posts tagged with

Comments

micajblock

What version of BIRT are you using?

spaddock

We are using BIRT 4.3.2 v2
 
-Shayne

micajblock

Average is a derived measure. Create a SUM measure and a COUNT measure. The the Average should be a derived measure by dividing the SUM by the COUNT.

spaddock

I had already created a derived measure. My issue is that with the totals for the group I want it to be an average of the computed derived averages. Instead it seems to be taking a total average of all the underlying data that made up those averages which is leading to precision issues of a couple of decimal points.
 
Instead of taking the three average scores and dividing by three it seems to be taking a sum and count for all the raw data that make up the group and dividing by that. Which is strange since I would have thought the cube would have aggregated everything already, makes me think that those totals go back to the dataset that made up the cube.
 
Let me know if I'm not being clear enough, I'm trying to explain the scenario the best I can.
 
-Shayne

micajblock

Unfortunately that is not how it works. Average by nature is a derived measure. A derived measure is not stored in the cube but calculated at run-time.For example in the attached report I am displaying the average of the product lines. The average of the average is 82,085.46, however the real average of the sales is 88,111.84. To my knowledge in a cross-tab there is no way to do an average of an average (which is what you are asking for). It is possible in a table though (also see attached report).
 
Let me know if this makes things clearer.