Hi,
I’ve created an Average for Last 6 months using Averagex, only issue being some days I have zero values, which I’d like to remove from the calculation. (I’m assuming Averagex takes into account the zeros?)
What is the best way to do this?
Average Units Last 6mths =
CALCULATE (
AVERAGEX ( Units, [Total Units] ),
DATESINPERIOD ( Date_Table[Date], TODAY() - 1, -6, MONTH )
)
Thanks
David
Hi @DavieJoe ,
Yes, the averagex does take into account the zeros (even tested with dummy data), it takes all values (which have a date) with the given condition Datesinperiod .
A possible solution is : add a filter with saying the unit value <> 0 , more filters can be added.
There are probably other solutions as well.
Average Units Last 6mths =
CALCULATE (
AVERAGEX ( Units, [Total Units] ),
DATESINPERIOD ( Date_Table[Date], TODAY() - 1, -6, MONTH ),
FILTER( Units, [Units] <> 0))
Kind regards, deltaselect
Unfortunately this does not work as when you add this measure with the filter you added I lose the static/flat line.
Thinking on it again, I could exclude weekends completely as Saturdays being it right down and Sunday’s are where all the zeroes are.
@DavieJoe AVERAGEX ignores BLANKS and not explicit Zeroes. Also, Context Transition on a FACT table?
Average Units Last 6mths =
CALCULATE (
DIVIDE ( [Total Units], COUNTROWS ( Units ) ),
DATESINPERIOD ( Date_Table[Date], TODAY () - 1, -6, MONTH ),
Units[Units] <> 0
)@DavieJoe No problem, regarding why @deltaselect’s answer gives zig zag lines is that when you use FILTER( Units, [Units] <> 0)) you are actually referring to the expanded table Units, which contains columns for the dates table.
DATESINPERIOD creates a filter context, and then second argument of CALCULATE also creates a filter context, and when it is time to merge both you have a list containing 6 months and then other aregument is filtered by the axis/external filter context, so for 9th may you have only 1 row of Units table visible and the corresponding value in the Dates table is also for 9th May.
Since CALCULATE evaluates its filter arguments in an AND operation, both filters are merged and all the values that intersect are applied to the filter context, and you only get 9th may and Unit <> 0 in filter context, in easy words you are basically plotting bar chart with line chart for the same points.
Conclusion - Never prepare filter context by using FACT table and same goes for DIMENSIONS in case of SNOWFLAKE schema
Hi @AntrikshSharma,
Thanks for your clear expert explanation, learned again. I am happy to use your formula.
What I do not understand is that my proposed formula seems to work fine in the dummy file as provided, it gives 2.091,05 in a flat line as outcome, the same outcome as from your formula, see attached:.
Containers Last 6mth with avgx.pbix (147.4 KB)
Thanks in advance, Jan van der Wind
I’ll also “fess” up and say that a colleague passed me this issue and I was given summarised raw data so it was one number for one day so when I joined my date table it was a 1-1 relationship so I copied some of the dates to get a 1-Many relationship but zeroed them myself
I still can’t get Filter Context into my head properly 
FYI – whenever Power BI creates a 1:1 relationship, I immediately change that to a 1:M unidirectional, and you can do so without adding any additional data even if the current correspondence is simply one to one.
Hi @deltaselect,
It is working correctly in this scenario because the only duplicates the table has are for 0, so when 0 is applied to filter context it filters 25 rows for each date from January 4th till February 1st, so 25 times 0 filters 25 rows, but the aggregation of 0 is also 0, but if this was any other number then the result would have been inflated.
To test I replaced 0 with 1 and here are the results:
Containers Last 6mth with avgx.pbix (131.7 KB)
Hi @AntrikshSharma , can you share a resource where I can deep dive into learning how to prepare filter context without using the fact table and dimensions?
