Skip to Content
0
Former Member
Jan 31, 2014 at 09:58 PM

4.0 Web Intelligence - Formula for Average Values

776 Views

Hello,

I have a Web report with three dimensions and one measure.

There can be multiple values of Dim2 under one value of Dim1, and multiple values of Dim3 under Dim2. I am able to get the correct average of Dim2 in the following formula.

Dim2Average =Average([Measure]) In (Dim2])

However, the following formula does not give me the expected results.

Dim1Average =Average([Measure]) In ([Dim1])

There are breaks in place on Dim1 and Dim2.

See Table Below:

Dim1 Dim2 Dim3 Measure

dim1 dim2_a dim3_aa 2

" " dim3_ab 2

" " dim3_ac 2

" dim2_a Average 2

" dim2_b dim_ba 3

" " dim_bb 3

" dim2_b Average 3

Dim1Average 2.43

Dim1Average is taking (2+2+2+2+3+3+3)/7 = 2.43

I need Dim1Average to be the average of 'dim2_a Average' and 'dim2_b Average', (3+2)/2, which would be 2.5

Is this possible using other logic in the formula?

Thanks.