SQLTeam.com | Weblogs | Forums

SQL Analysis Services: Calculated Member - SUM & AVERAGE


#1

Hello All -
I'm a bit new to analysis services and I am seeking help to create a calculated member that will add up multiple columns of data and then generate an average using another set of calculated columns. I seem to get half way there however my average will change based upon my cube dimension. Hopefully the below makes sense.

Here is my data (36 rows):

User Date R_Count_001 R_Count_002 R_Count_003 R_Count_004 R_Count_005 R_Count_006 H_Count_001 H_Count_002 H_Count_003 H_Count_004 H_Count_005
User A 2014-08-19 0 149 24 10 0 0 8 0 0 0 8
User A 2014-08-20 0 1324 82 0 0 0 8 0 0 0 8
User A 2014-08-21 0 203 657 0 0 0 8 0 0 0 8
User A 2014-08-22 0 900 513 1 0 0 8 0 0 0 8
User A 2014-08-25 0 66 339 0 0 0 8 0 0 0 8
User A 2014-08-26 0 836 1095 0 0 0 8 0 0 0 8
User A 2014-08-27 0 142 766 0 0 0 8 0 0 0 8
User A 2014-08-28 0 82 673 0 0 0 8 0 0 0 8
User A 2014-08-29 0 148 703 2 0 0 8 0 0 0 8
User A 2014-09-02 0 19 1032 0 0 0 8 0 0 0 8
User A 2014-09-03 0 0 1731 0 0 0 8 0 0 0 8
User A 2014-09-04 0 56 735 2 0 0 8 0 0 0 8
User A 2014-09-05 0 2 723 0 0 0 8 0 0 0 8
User A 2014-09-06 0 23 298 0 0 0 8 0 0 0 8
User A 2014-09-08 0 205 303 0 0 0 8 0 0 0 8
User A 2014-09-09 0 71 164 0 0 0 8 0 0 0 8
User A 2014-09-10 0 81 254 0 0 0 8 0 0 0 8
User A 2014-09-11 0 67 157 0 0 0 8 0 0 0 8
User A 2014-09-12 0 0 186 0 0 0 8 0 0 0 8
User A 2014-09-15 0 53 481 0 0 0 3 0 0 5 8
User A 2014-09-16 0 104 241 0 0 0 8 0 0 0 8
User A 2014-09-17 0 179 247 0 0 0 8 0 0 0 8
User A 2014-09-18 0 230 521 110 0 0 8 0 0 0 8
User A 2014-09-19 0 58 118 0 0 0 7 0 0 0 7
User A 2014-09-29 0 107 3 0 0 0 8 2 0 0 10
User A 2014-10-01 0 224 29 0 0 0 8 0 0 0 8
User A 2014-10-03 0 177 16 0 0 0 8 0 0 0 8
User A 2014-10-06 0 70 100 1 0 0 8 2 0 0 10
User A 2014-10-07 0 200 187 0 0 0 8 0 0 0 8
User A 2014-10-08 0 188 5 70 0 0 7.5 0 0 0 7.5
User A 2014-10-13 0 0 0 0 140 0 8 0 0 0 8
User A 2014-10-14 0 0 0 0 141 0 8 0 0 0 8
User A 2014-10-17 0 398 89 0 0 0 6.5 0 0 0 6.5
User A 2014-10-21 0 895 26 0 0 0 4.5 0 0 0 4.5
User A 2014-11-13 152 0 0 0 0 0 8 0 0 0 8
User A 2014-11-14 296 0 0 0 0 0 8 0 0 0 8

My calculations is as follows:
((R_Count_001+R_Count_002+R_Count_003+R_Count_004+R_Count_005+R_Count_006)/(H_Count_001+H_Count_002+H_Count_003+H_Count_004+H_Count_005))

Essentially the math comes: AVG SUM 2,686 divided by ROW COUNT 36 = 74.6

This calculation displays the correct averages when my cube shows the individual break down based upon USER & DATE however when I aggregate the average by USER and eliminate DATE my average recalculates. Essentially the average changes from 74.6 to 72.43

I understand why this happens however I can't seem to figure out how to rewrite my calculation so that my aggregate average shows up as 74.6.

Any help is greatly appreciated.