set analysis with distinct and multiple parameters

chrismtb — Fri, 12 Aug 2022 13:02:04 GMT

Hi I have the following data set (boiled down to a much more simple view):

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?

Re: set analysis with distinct and multiple parameters

BrunPierre — Fri, 12 Aug 2022 13:23:24 GMT

This will display distinct amount i.e. 50

=sum(distinct aggr(sum({$<Classification={'A'},Level={'1'}>}Budget),ID1,ID2))

BrunPierre — Thu, 25 Aug 2022 14:06:49 GMT

@chrismtb Did it solve your problem? please mark accordingly.