Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate daily average with several records per day

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])))

 

LauraGR_2-1678948297735.png

 

2 REPLIES 2
amitchandak
Super User
Super User

@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
Not applicable

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!

LauraGR_0-1679268212608.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.