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?
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 )
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:
12-06-2018 02:07 PM
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.