Nested aggregation in pivot table

[35677]

Hi Expert,

When I am using nested aggregation in pivot table it is returning value as Zero for some dimensions.

sum(aggr(max({<$1,$2,[Type]={'S01'},[XYZ Value]={0}>}[Days]),[ID]))
/
count(distinct{$<$1,$2,[Type]={'S01'},[XYZ Value]={0}>}[[ID])

The above measure returning me the exact value only for the first dimension while for the remaining dimensions it is returning zero. 

I have used the nodistinct in my expression it is returning the whole value but I need only the distinct value.

Please guide me to solve this issue. Thanks in advance

[brunobertels]

Hi 

because of the structure of pivot table your nested agregation must include all the dimension of your pivottable 

[35677]

Hi Bruno,

Even If I add all the dimensions in aggregation function still the same result.

[brunobertels]

hi 

you add aggr and all dim in both mesure : 

mesure  1 : sum(aggr(max({<$1,$2,[Type]={'S01'},[XYZ Value]={0}>}[Days]),[ID]))
/
mesure 2 : count(distinct{$<$1,$2,[Type]={'S01'},[XYZ Value]={0}>}[[ID])

not sure but try :

sum(aggr(max({<$1,$2,[Type]={'S01'},[XYZ Value]={0}>}[Days]),[ID]))
/
aggr(count(distinct{$<$1,$2,[Type]={'S01'},[XYZ Value]={0}>}[[ID]),Days]),[ID])

[35677]

Hi,

Tried the logic which you mentioned however it is not working. 

If I use only the second measure, it is working fine.

If I use the first measure alone, it is creating this issue.

measure  1 : sum(aggr(max({<$1,$2,[Type]={'S01'},[XYZ Value]={0}>}[Days]),[ID]))

Note: ID field which I am pulling is a inline dimension.