Different calculations at different pivot points on pivot chart

[#1]

Hello,

I have a pivot chart object that has 3 dimensions.  I have Month and Year nested together on columns so that the year value is based on the month values.  Currently I have 3 calculations on the row members that use the average function for each month.  For example this is what it does:

JAN    FEB     MAR  Q1

10       10        10       10

What I want it to do is:

JAN         FEB            MAR       Q1

10                10                  10           30

What kind of sytnax ( I assume an if statement) do I need to get this to work?

Thanks,

Joe

[johnw]

No if() necessary if I understand what you want.  The expressions would look something like this:

sum(aggr(avg(Value),Dimension1,Dimension2,Dimension3))

[#3]

Hi John,

Thanks for the quick reply! here is the calc I am using:

avg( aggr(distinct sum({1<MEASURE_KEY={2}>} Value), SCENARIO_DESC,MONTH,DEPT_NAME) )

This will give me the average for each month, but will average out the yearly value as well

avg( aggr(distinct sum({1<MEASURE_KEY={2}>} Value), SCENARIO_DESC,YEAR,DEPT_NAME) )

If I try this one, it will give me the correct total at the year level, but the monthly values will be blank

avg( aggr(distinct sum({1<MEASURE_KEY={2}>} Value), SCENARIO_DESC,MONTH,YEAR, DEPT_NAME) )

This will give me the same result as the first calc.

Thanks for any help!