Create a fixed measure which doesn't change with time filter

[Anonymous]

Hello,

i want to create a measure which gives the average value for a column(excluding any 0) and i'll add that measure to a line chart. once i applied date filter it should still show a fixed line but for filtered columns.

date	value
1-Jul	9
2-Jul	1
3-Jul	1
4-Jul	8
5-Jul	0
6-Jul	4
7-Jul	4
8-Jul	0
9-Jul	9

if i select all date range it should show a fixed line chart of value 5.14

if i select dates between 7/1-7/4 it should show a fixed line chart of value 4.75

below is the graph i want to get. blue line represent values over time and red line reprecent fixed average value for selected date range.

heighly appriciate any help

thanks 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create a measure

average = CALCULATE(AVERAGE(Sheet2[value]),FILTER(ALLSELECTED(Sheet2),Sheet2[value]<>0))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-juanli-msft  Thank you very much. it works perfectly