Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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:
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |