
Re: Can you do a different aggregate calculation in the total of a pivot table .
Lucian Cotea Jul 19, 2013 11:22 AM (in response to Oliver Gomes)=if( Dimensionality() = 1
, sum(Qty)
, avg(Qty)
)

Re: Can you do a different aggregate calculation in the total of a pivot table .
Gysbert Wassenaar Jul 19, 2013 11:24 AM (in response to Oliver Gomes)You can use the dimensionality() function. Something like if(dimensionality()=0, avg(aggr(sum(Sales),Product)),sum(Sales)) in a pivot table that has Product as dimension and Sales as a measure for the calculations.

Re: Can you do a different aggregate calculation in the total of a pivot table .
Cesar Accardi Jul 19, 2013 11:34 AM (in response to Oliver Gomes)Hi Oliver,
Since the pivot table doesn't have the option to change the Total mode of the expressions in the properties dialog like the straight table, you will have to do that in your expressions. If it's just a simple average of the calculations you can achieve that using the AGGR function (advanced aggregation). For example:
avg(aggr(sum(Value),Dimension))
Please let me know if you need more information,
Cesar

Re: Can you do a different aggregate calculation in the total of a pivot table .
Oliver Gomes Oct 8, 2013 1:41 PM (in response to Cesar Accardi)select COUNT (*) ,MONTH(c.created_at)
from tbStageF_content (nolock) c
where
creator_source=100
and year(c.created_at) =2013
and MONTH(c.created_at) >= 1
group by
MONTH(c.created_at)
order by 2


Re: Can you do a different aggregate calculation in the total of a pivot table .
Oliver Gomes Oct 8, 2013 12:47 PM (in response to Oliver Gomes)Thanks for your suggestion
my calc is a little different
=if (RegMonth =ActivityPeriod30,200,sum(Rev))
the resulting sum will always be the else condition .
Should the if condition be in set analysis . How would the calculation be written?