Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table with two dimensions. I have an expression that calculates average in a field, I found that using avg(Field) doesn't give me the correct subtotal/total by dimension (using show partial sums). I am using the expression:
avg(aggr(avg(Field), Dim1, Dim2))
I get the correct subtotal (Dim1) but the total (Dim2) is less than it should be.
Any help will be appreciated. Thanks
May be this
Avg(Aggr(Avg(Aggr(Avg({<Dim2 = {"=Avg(FIELD) > 0"}>}FIELD), DIM1, DIM2)), DIM2))
try this sum(aggr(avg( field), dim1, dim2 .....))
That gives me a total of the averages. I am looking for an average of the averages. For example your result gave me the total of my averages at 235.95 for dim1 what I want is that divided by the count of the dim1 (7) for a result of 33.71. Dim1 result is fine, it's dim2 total I'm not getting. It should be sum of subtotals for dim1 divided by count of dim 2.
Thanks for the suggestions.
can you provide some sample data
Dim2 | Dim1 | Avg TAT | |
A | 22.80 | ||
B | 31.11 | ||
C | 7.98 | ||
D | 5.36 | ||
E | 1.38 | ||
F | 7.72 | ||
G | 1 | 6.50 | |
G | 2 | 21.74 | |
G | 3 | 5.50 | |
G | 4 | 147.73 | |
G | 5 | 32.83 | |
G | 6 | 16.72 | |
G | 7 | 4.93 | |
G | Total | 33.71 | Correct |
H | 32.21 | ||
I | 18.29 | ||
J | 22.12 | ||
Total | 23.15 | Incorrect |
Here you go
try this AVG(AGGR(AVG(aggr(avg(FIELD),DIM1,DIM2)),DIM2))
Getting close. Sum of subtotal averages is 182.68. Count of dim2 is 10. I should be getting total average of 18.27.
Thanks
what is the answer are you getting now ?
16.61
I would suggest use dimensionality to control what is displayed in the subtotal and total fields, eg
if (dimensionality()=1, aggr(avg()), avg())