7 Replies Latest reply: Sep 30, 2014 1:49 AM by Prashant Sangle

# Calculate average per month based on the count of months

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!

• ###### Re: Calculate average per month based on the count of months

Hi

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

Jonathan

• ###### Re: Calculate average per month based on the count of months

Hi,

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

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

• ###### Re: Calculate average per month based on the count of months

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)

• ###### Re: Calculate average per month based on the count of months

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.

• ###### Re: Calculate average per month based on the count of months

Hi,

Try this expression

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

Regards,

Jagan.

• ###### Re: Calculate average per month based on the count of months

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é

• ###### Re: Calculate average per month based on the count of months

Hi,

Try like

Sum(Total<MonthYear> [Rebate Units])

Regards