7 Replies Latest reply: May 9, 2017 9:33 AM by Zied Ahmed

# Average of Sum

Hello,

I have this pivot table and i would like to display the average of the sum of all Months :

this is the expression used :

if (Dimensionality()=1, Avg(Aggr( Count({<[Year of date]={"\$(=max([Year of date]))"}>}Num_Dossier)

/

Count({<[Year of date]={"\$(=max([Year of date]))">}distinct (Plaque)), Plaque,[Month of date] ,[Year of date])),

Count({<[Year of date]={"\$(=max([Year of date]))"},>}Num_Dossier)

/

Count({<[Year of date]={"\$(=max([Year of date]))"}>}distinct (Plaque)))

He display the value of janv and not the avg

Regards

• ###### Re: Average of Sum

You may have encountered a granularity mismatch, won't you need to add Materiaux field to the Aggr() dimensions?

if (Dimensionality()=1, Avg(Aggr( Count({<[Year of date]={"\$(=max([Year of date]))"}>}Num_Dossier)

/

Count({<[Year of date]={"\$(=max([Year of date]))">}distinct (Plaque)), Materiaux, [Month of date] ,[Year of date])),

• ###### Re: Average of Sum

Hello Stefan in the aggregation i added Materiaux and i removed plaque but the same issue,(it display the values of the first month

• ###### Re: Average of Sum

You need to use the field that is used to display Materiaux dimension (so not the dimension label if it differs from the field name).

Could you post a small sample?

• ###### Re: Average of Sum

Hello Stefan,

example:

for the first materiaux named FURGON ,in 2017 i need to have the average of (2.96 +3.21 +3.76 +3.24 + 1.20)/5=2.87

Regards

• ###### Re: Average of Sum

Ok, but actually I was asking for a small sample application file or sample set of your data records.

• ###### Re: Average of Sum

do you have please gmail id ?

• ###### Re: Average of Sum

I think i find the solution in the aggregation i need to put the year and the materiaux