problems with average calculations that do not give correct value

I am currently building a cube and I have two measures that I need to calculate the average for (i.e. columns measureA and measureB).

measureA is a Sum of a value and measureB is the count of the number of occurences of the value.

I define a calculated Member that employs the calculation of measureA/measureB. This gives me an average that I want to roll up.

At the lowest level the calulation is correct, however as the rollups happen the calcultion becomes invalid. At the (all) level the calculation is quite far out.

The calculated member simply gives me the (All)measureA/(All)measureB.

Can anyone point me in the direction of how I can get a calculation of the individual measureA/measureB rolled up correctly giving me an overall average?

COUNT takes into account empty cells, so when you rollup, you are considering more cells than you should.

Use AVG, or filter out empty cells when using COUNT

See

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=390791&SiteID=1

Hope this helps,

Santi

|||

Thanks Santi That did help me.

What I was trying to do was more complex than simply averaging a single column data and after studying the problem I found that it was far too complex and that using Avg was much better.

Many thanks

Rob