How to get the right avg of a sum in a straight table???

[marcel_olmo]

Hey guys I have a straight table with Total Sales per Year.

The calculation of the Total Sales per Year is fine, but my desired result would be to add the total avg result in the first non dimensionality line, but I still don't get how to do it.

Here's what I'm doin :

I've a List Box Selection to select Years, and two straight tables :

The result of the first straight table is obviously wrong, and the result of the second straight table are fine only if I don't select any year. And I would like to get always the avg of all the years.

This is what happens if I select 2011 :

Now both results of straight tables are wrong. The second one gets the results of the year selected.

Now I'm gonna show you what I'm doin in both straight tables :

First straight table :

/*
If dimensionality is not 0, I got the usual sum with Year=, to keep the dimension year.
If dimensionality is 0, I got the same doin a division per 6 (number of available years)
*/

if(dimensionality()<>0, sum({$< Year= >} [Amount] ) , sum({$< Year= >} [Amount] ) / 6 )

Second straight table :

/*
If dimensionality is not 0, I got the usual sum with Year=, to keep the dimension year.
If dimensionality is 0, I got the avg of the aggregate of the sum() with the dimension Year )
*/

if(dimensionality()<>0, sum({$< Year= >} [Amount] ) , avg(aggr( sum({$< Year= >} [Amount] ), Year)) )

My question is : how can I keep the right avg of the sum of all years, even when a year is selected ??

Many thanks in advance!!!!

[tresesco · Accepted solution]

Hi Marcel,

Probably you have to include the SET {$<Year=>} in AGGR and AVG functions too.

Regards, tresesco

[marcel_olmo]

Thanks Tresesco, you're right, I've to copy the same set analysis in the avg, as it seems, in the aggr is not necessary.

Regards, Marcel.