Forum Discussion
Bar and Line Chart With Date – Fixing Average Line Not Respecting Date Filters in Stacked Co
Hi, I hope someone can help.
I was trying to add an average line to a stacked column chart in Power BI. Initially, the measure either produced a non-flat line or didn’t match the expected average due to filtering context issues.
The key challenges were:
- The average being recalculated per axis/legend instead of returning a single value
- Hidden filters and visual context affecting results
- The measure not respecting page-level date slicers
The solution involved:
- Using AVERAGEX over ALLSELECTED to remove axis context while keeping slicers
- Making sure filters (like VisitorType or other legend fields) were removed inside the calculation where necessary
- Most importantly, switching from the fact table date column to the Date table column in ALLSELECTED so the measure correctly honours page-level filters
Once aligned, the result should provide:
- A correct average value
- A flat horizontal line
- Full responsiveness to slicers and filters
I've just found the issue.
I was using the code above (with the flat average line).
However, I found I was using two different dates, in the code
DIM_Calendar[Date]In the chart I was using,FACT_BadgeRecordsSlim[Date] - I've now changed this and it has removed the months that are filtered out.Hope this helps someone else.ThanksRob
4 Replies
This updated code provides the flat Average Line, but the date filter is not honoured.
msAvgFilterRank =VAR AvgValue =CALCULATE(AVERAGEX(ALLSELECTED(DIM_Calendar[Date]),CALCULATE(SUM(FACT_BadgeRecordsSlim[Count]),FACT_BadgeRecordsSlim[Count] > 0,FACT_BadgeRecordsSlim[RankFilter] = TRUE())),REMOVEFILTERS(FACT_BadgeRecordsSlim))RETURNAvgValueHi robdwright65,
Did you try to add a Reference line? You need to choose an Average line type and a measure in Series (the average of this measure will be taken).
Hi ERD , thanks for looking at this.
Yes, I tried the standard route of adding an average line, but the value is incorrect as there are other calculations that need happen to provide the true average. We are removing weekend and low count days.
Thank Rob
I've just found the issue.
I was using the code above (with the flat average line).
However, I found I was using two different dates, in the code
DIM_Calendar[Date]In the chart I was using,FACT_BadgeRecordsSlim[Date] - I've now changed this and it has removed the months that are filtered out.Hope this helps someone else.ThanksRob
