Total Average of Averages

[ashokunbi]

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

[ashokunbi]

Sunny,

I’m trying to get the same result for a subset of the data using an if statement and the if statement is being completely ignored as I am getting the same results as I did for the whole data.

I did:

If(FieldB = ‘ABC’, Avg(Aggr(Avg(Aggr(Avg({<Dim2 = {"=Avg(FIELD) > 0"}>}FIELD), DIM1, DIM2)), DIM2)),0)

After that didn’t do the trick, I put the expression you gave me in a variable and tried this:

=If(FieldB = ‘ABC’, $(vVariable),0)

Still no change.  Any ideas?

[sunny_talwar]

Try this

Avg(Aggr(Avg(Aggr(Avg({<Dim2 ={"=Avg({<FieldB = {‘ABC’}>} FIELD) > 0"}, FieldB = {‘ABC’}>}FIELD), DIM1, DIM2)), DIM2))

[ashokunbi]

Nothing. The whole chart got wiped out except for Dim2 header

[sunny_talwar]

Something looks wrong about the quotes, can you try this

Avg(Aggr(Avg(Aggr(Avg({<Dim2 ={"=Avg({<FieldB = {'ABC'}>}FIELD) > 0"}, FieldB = {'ABC'}>}FIELD), DIM1, DIM2)), DIM2))

[ashokunbi]

Showed data for only 1 value for dim2 instead of for all 10 and I'm pretty sure the avg shown is wrong. Thanks.

[ashokunbi]

Any ideas why I'm only getting one, Sunny?

[ashokunbi]

Sunny, I deleted the chart and recreated it from the working chart and used the last expression and it worked and the numbers are accurate. I noticed that both charts only display the last value for dim1 when you expand dim2 but the values are accurate. I will be able to fix the expansion issue. Thanks a lot for your help. I really appreciate it.