Calculate average per month based on the count of months

[#1]

Hi there,

I'm trying to calculate an a !verage per month (= the sum of ... divided by number of months available) but it doesn't work. Anyone any suggestions what I'm doing wrong?

If (in a text box) I apply the calculation "count(distinct(Month))" it gives me the number "2" which is correct since there are only two months of data available. However, applying the same expression in a pivot table doesn't work.

Much appreciated if someone can guide me into the right direction.

Cheers!

[jonathandienst]

Hi

Your sample has a bunch of pivot tables. I can't see which one needs what....

Jonathan

[#3]

Hi,

Pivot table will calculate row basis....try something like this.

sum(  )/aggr(NODISTINCT Count(DISTINCT Month),YEAR)

[Anonymous]

in the Pivot table you have the months in columns.

if you divide by total you should get the rcorerect result

sum(sales)/Count(total distinct monthnumber)

[#5]

Thanks for thinking with me. Several of the proposals did not work and to clear things up, I've attached a simplified file which includes the individual steps to be taken.

[jagan]

Hi,

Try this expression

=((suM([Sales (units)]) + sum(SOH)) /((suM([Rebate Units]) /Count(TOTAL MonthYear))))* 4

Regards,

Jagan.

[#7]

Hi Jagan,

The issue with the 'Count(Total MonthYear)' is similar to the 'Count(Distinct(Month)) as it doesn't split the sum of Rebate Units by this number. In the solution you've provided, it will split the Rebates (per month) by 7 whereas the correct calculation should be the sum of Rebate Units (for the months measured) divided by 2.

Regards,

René

[PrashantSangle]

Hi,

Try like

Sum(Total<MonthYear> [Rebate Units])

Regards