Average of 24 hours with if statement

[skfinegan]

I have days worth of data (24 hrs/day). I am trying to average the values within a 24 hour period while also excluding values less than 0.4. Can anyone provide any guidance on how I would do this?? 

Do I use IF statements with the AVERAGE function? 

 Please help. Thanks! 

[V-lianl-msft]

Hi @skfinegan ,

You can use the average function to calculate the average value, filter the date column with the allexcept function, and add the filter condition of kW > = 0.4.

avg_kw = CALCULATE(

AVERAGE('table'[kw]),

FILTER(ALLEXCEPT('table','table'[date]),'table'[kw]>=0.4))

You can also refer to the pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

As a new column, this will give an average for each day in table excluding some values. Please change column name and condition as per need

Avg column = averagex(filter(table,table[date]=earlier(table[date])),if(table[KW value]<.04 , blank(),table[KW value]))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[skfinegan]

Thank you so much! This worked. 

If I want to graph the avg each day how do I do that without it adding the average together for all 24 hours? That may be a dumb question, but I can't figure it out. 

Thank you!