Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi There,
Within a pivot table, I am struggling to create a virtual table with Aggr() from data that uses both:
- filtered data from the pivot table
- data from a table that is not linked to the above
The problem is as follows. I have 2 tables that are not linked:
Table1 values:
[ID];[Value]
A;1
A;2
A;3
B;4
B;2
B;6
Table 2:
[Event]
0
1
2
3
4
The pivot table I am trying to create should show:
DIMENSION= [ID], i.e. A;B
Measure: SUM(Pow([Event],avg([Value])), in this case, for A: 0^2 + 1^2 + 2^2 + 3^2 + 4^2 and for B: 0*4 +1^4 + 2^4+3^4+4^4
I tried writing the Measure as :
Sum(Aggr(
Pow([Event],avg([Value])),
[Event])
but it doesn't work at all. It seems that it cannot built that aggregate based on [Event] as parameter since Table 2 is not linked/concatenated/joined to Table 1.
I've tried using "total" in all sort of combinations, but the only one that yields a result is:
Sum(total Aggr(
Pow([Event],avg([Value])),
[Event])
However, in this case the avg([Value]) is no longer per ID (2 for A and 4 for B) but the overall average (3).
Any ideas on how to bridge this problem? how can I use both [Events] that is independent and [Value] that is filtered per [ID]?
thanks!
Try this
Sum(Aggr(
Pow([Event], Avg([Value])),
ID, [Event]))