Monthly Average without Every Month in Data

[cranium144]

Hi.

I have an example where I'm doing a calculation to get the average number sold per month.  The calculation is:

sum(qtySold) / count(DISTINCT MonthYear)

However, this causes an issue when I select say Year 2017.  A particular customer only had sales in 6 months.  So here is what I'm getting as a result, and what I want, assuming a Qty Sold of 1,200.

Actual Result: 200 / month

Wanted Result: 100 / month

Any suggestions on how to get the 'true' average based on the total month span?  (including across years)

Thanks!

Rick

[sunny_talwar]

May be this

Sum(qtySold)/Count(DISTINCT TOTAL MonthYear)

[cranium144]

I like the idea.  However, I still received the same result.  Only 6 MonthYear's of data exist.  I'm attempting to determine how many possible MonthYear's exist in the selections.

[sunny_talwar · Accepted solution]

Have you selected a particular customer? May be ignore selection in Customer field

Sum(qtySold)/Count(DISTINCT TOTAL {<Customer>} MonthYear)

[cranium144]

That seems to have done the trick.  Thank you!