- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Simple AVG function

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 10:48 AM

I am using a simple average calculation and it seems when a month there are no values it is excluding that month from the avg all together... is there a way to use the average function and it take every single month into account and divide by that number even if that month is blank and has no values?

## Re: Simple AVG function

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 12:03 PM

One way to ensure that blanks are treated as zeros for the purpose of the average calculation is to add zero to the expression being averaged.

At the same time, I would simplify your formula slightly to something like this:

= AVERAGEX ( VALUES ( 'Calendar'[Month] ), [CALC_FIELD] + 0 )

Regards,

Owen

## Re: Simple AVG function

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 01:24 PM

Here's how I tackled this:

1. Add an index column to your table, usually a Calendar Table ( super easy with power query)

Create simple measure for Total Sales:

Total Sales = sum ( 'Avg Dont Skip'[Sales] )

Create a cummulative Sales and Cumulateive Rows measures

Cumu Sales = CALCULATE ( [Total Sales], FILTER ( ALL ( 'Avg Dont Skip' ), SUM ( 'Avg Dont Skip'[Index] ) >= 'Avg Dont Skip'[Index] ) ) Cumu Rows = CALCULATE ( COUNTROWS ( FILTER ( ALL ( 'Avg Dont Skip' ), 'Avg Dont Skip'[Index] <= SUM ( 'Avg Dont Skip'[Index] ) ) ) )

so we have our numerator and denominator, then just divide from there:

Avg Sales = DIVIDE([Cumu Sales] , [Cumu Rows] )

Then the final table:

## Re: Simple AVG function

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-06-2018 02:07 PM

## Re: Simple AVG function

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 10:51 AM

Also, the easiest way to see this is to add a month filter and you will notice that not in all months will we have values... however, I still want the average to be over the 10 month timespan even if we do not have results so in the sample below the average would be 19.07.