Set Analysis with AGGR excluding fields

[Josgac]

Hello Everybody,

I am trying to set a Master Measure with set Analysis and the following function:

SUM(AGGR(SUM({$<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

It is a table with multiple MB values ans I only want the sum of Unique Hours per MB in 2019. The problem is that I don't want the selection to be affected by Dec1 and Dec2 fields, but this last condition doesn't work and the sum of hours is affected by them. Can you help me?

Thanks a lot

[Anil_Babu_Samineni · Accepted solution]

Sometimes simple suggestion works like

SUM(AGGR(SUM({1<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

Sometimes, Outer aggregation also needed. Not sure, How you are doing mathematically

SUM({$<[Year]={2019},Dec1= ,Dec2= >} AGGR(SUM({1<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

Or

SUM({1<[Year]={2019},Dec1= ,Dec2= >} AGGR(NODISTINCT SUM({1<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

[Josgac]

Hi Anil, 

I tried to add the outer aggregation and it worked. 

SUM({$<[Year]={2019},Dec1= ,Dec2= >} AGGR(SUM({$<[Year]={2019},Dec1= ,Dec2= >}DISTINCT Hours),MB))

Thank you very much for your quick response

Regards,