Calculate daily average with several records per day

[Anonymous]

Hi There!

I have a table  with several temperature records per location per day at different times. I'm trying to calculate the daily average, so I can run other calculations i.e. difference from annual average, cumulative difference, etc, but I can't get this daily average right.

I have a calendar table with both date_time and date_only, and tried several methods posted in the forum, but I can't get a static average per day.

Please see some sample data below. Thanks in advance for your help!  🙂

1. AVERAGEX ( VALUES (Table[Date_only]), [D])

2. CALCULATE (

AVERAGE(Table[D] ),

FILTER ( ALLSELECTED ( Table ), Table[Date_only ]=EARLIER(Table[Date_only ]

)))

3. Avg_D = CALCULATE(AverageX(Values('Table'[Date_only]), CALCULATE(SUM('Table)'[Values]))) ,filter(all('Table'[Date_only]),'Table)'[Date_only] <=max'Table'[Date_only])))

[amitchandak]

@Anonymous , if you plan to overall number

then you need a measure like, assuming D is a measure

calculate(AVERAGEX ( VALUES (Table[Date_only]), [D]), allselected())

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Thanks for your response @amitchandak! My data was in a column format so I created a measure (D_Measure) so I could apply your suggested measure: calculate(AVERAGEX ( VALUES (Table[Date_only]), [D]), allselected())

It looks like it's calculating an average over the whole dataset, not by day. 

Thanks in advance for your suggestions!