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