Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Help! This is driving me mad!!!
I have a straight table with 2 dimensions and 2 expressions
I would like the 2nd expression to only take Dim1 into account. However, I get the following:
Dim1 | Dim2 | AVG({<GEN={'ALL'}>}AP) | AVG({<GEN={'ALL'}>} TOTAL <Dim1> AP) |
---|---|---|---|
1 | A | 4 | 4 |
1 | B | 1 | 1 |
2 | B | 2 | 2 |
2 | C | 5 | 5 |
When I change the dimension in the modifier
Dim1 | Dim2 | AVG({<GEN={'ALL'}>}AP) | AVG({<GEN={'ALL'}>} TOTAL <Dim2> AP) |
---|---|---|---|
1 | A | 4 | 4 |
1 | B | 1 | 1.5 |
2 | B | 2 | 1.5 |
2 | C | 5 | 5 |
I get what I would expect (see values for Dim2=B).
But with the first table (with Dim1 in modifier), I am not getting what I would expect.
What I want to see is:
Dim1 | Dim2 | AVG({<GEN={'ALL'}>}AP) | AVG({<GEN={'ALL'}>} TOTAL <Dim1> AP) |
---|---|---|---|
1 | A | 4 | 2.5 |
1 | B | 1 | 2.5 |
2 | B | 2 | 3.5 |
2 | C | 5 | 3.5 |
Where am I going wrong?!
Another option could be to use Aggr(),
Aggr(NODISTINCT Avg({<GEN = {'ALL'}>} AP), Dim1)
Would you be able to share a sample where you seeing this issue?
The expression seems to be correct.
change the expression to sum() / Count()
sum({<GEN={'ALL'}>} TOTAL <Dim1> AP) |
---|
/
Count({<GEN={'ALL'}>} TOTAL <Dim1> AP) |
---|
But I don't see why Avg() won't work here
Another option could be to use Aggr(),
Aggr(NODISTINCT Avg({<GEN = {'ALL'}>} AP), Dim1)
Agreed that it seems to be the correct expression, so no idea why it won't work!
But the Aggr() function has worked to give me the outcome I was expecting, so thank you very much!