Hi I have the following data set (boiled down to a much more simple view):
| ID1 |
ID2 |
Classification |
Level |
Budget |
| 1 |
11 |
A |
1 |
50 |
| 1 |
12 |
A |
1 |
50 |
| 1 |
13 |
A |
1 |
50 |
| 2 |
21 |
B |
1 |
50 |
| 3 |
31 |
B |
1 |
100 |
I am trying to do a set analysis on the budget but i only want to include distinct values from ID1 field.
within my analysis i also need to check the classification and the level during the calculation.
I have tried a few options but to no avail... my latest attempt is:
=sum(distinct aggr(sum({$<Classification={'A'},Level={'1'}>}Budget),ID1))
however this adds the budgets together for all items in classification A, Level 1 despite having the same ID1 value.
I cant use distinct on the budget amount as there may be several records within the same classification and level with the same budget figure.
Can anyone help me?
