Average in total of pivot table

[Anonymous]

Hello,

I'm trying to present an average number (average of the selected periods) in the total section of a pivot table. Pls see also the enclosed picture.

The set analysis I am using is the following for the number of fte's

=Sum({<Metric={"FTE's Operationeel","FTE's Directe Overhead","FTE's Indirecte Overhead"},PeriodType={[Current]},Cal_Year=,Fis_year=,Fis_year2=,Cal_Month=,Cal_MonthYear=,Fis_period=,Date=,AsOfPeriod={'>=$(=vMaxDateBegin)<=$(=vMaxDate)'} >} Bedrag)

This gives me the total fte's per period . But off course in the total section I get a total number where I need an average.

Furthermore I am using the following set analysis for the total revenue

=Sum({<Resultaatgroep5={[Netto omzet]},PeriodType={[Current]},Cal_Year=,Fis_year=,Fis_year2=,Cal_Month=,Cal_MonthYear=,Fis_period=,Date=,AsOfPeriod={'>=$(=vMaxDateBegin)<=$(=vMaxDate)'} >} Bedrag)

And by dividing revenue by number of fte's I get revenue by fte. This goes well. I just need an average number presented in the total section,

Any thoughts or clues are welcome...

Thx,

Rudi

[adamdavi3s · Accepted solution]

Hi Rudi,

Have you looked into the 'dimensionallity()' function?

You can use this to force different formula at different levels.

Just start by adding dimensionallity() as a column, then find the level at which you want to alter the formula and use an IF to get around it

IF(dimensionallity = x, avg(blahblah), yourexistingformula)

[Anonymous]

Thx Adam! It worked...