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
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?
Try this
Avg(Aggr(Avg(Aggr(Avg({<Dim2 ={"=Avg({<FieldB = {‘ABC’}>} FIELD) > 0"}, FieldB = {‘ABC’}>}FIELD), DIM1, DIM2)), DIM2))
Nothing. The whole chart got wiped out except for Dim2 header
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))
Showed data for only 1 value for dim2 instead of for all 10 and I'm pretty sure the avg shown is wrong. Thanks.
Any ideas why I'm only getting one, Sunny?
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.