Average of Measure

[Anonymous]

I am working on a sensor data to find the average sound of each working hour and working days for a month, for example, the average sound_mean of 9 o'clock for 30 days.

I can filter the working hours using a page-level filter...

Example:
Date              Hour   sound-mean
2018-09-01    08       15
2018-09-02    08       52
2018-09-03    08       150

.

.

.
2018-09-01    09       10
2018-09-02    09       20
2018-09-03    09       25
2018-09-04    09       50
To get the avg of 00:00, first I need to sum all of the sound_mean that happened at 00:00 for each day and then perform the avg.
So, 08= avg(15+52+150+......)

      09=avg(10+20+25+50+...)


Thanks

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my tets, you could refer to below formula:

Measure = CALCULATE(AVERAGE(Table1[sound-mean]),FILTER('Table1','Table1'[Hour]=MAX('Table1'[Hour])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[v-danhe-msft]

Hi @Anonymous,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[Anonymous]

The first solution gives me the correct results, but the second one doesn't work correctly it still gives me results for weekends and shows blank values for some weekdays.

[Anonymous · Accepted solution]

I adjusted the formula like below, and it works fine.

Measure = CALCULATE(AVERAGE(Table1[sound-mean]),FILTER('Table1','Table1'[Hour]=MAX('Table1'[Hour])&&WEEKDAY('Table1'[Date];2)<=5))

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my tets, you could refer to below formula:

Measure = CALCULATE(AVERAGE(Table1[sound-mean]),FILTER('Table1','Table1'[Hour]=MAX('Table1'[Hour])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

Thank you for your reply!

I don't understand what Max function does in this formula, how can I filter the working days(Monday to Friday?

[v-danhe-msft]

Hi @Anonymous,

You could modify the formula as below:

Measure = CALCULATE(AVERAGE(Table1[sound-mean]),FILTER('Table1','Table1'[Hour]=MAX('Table1'[Hour])&&WEEKDAY('Table1'[Date])<=5))

Result:

Regards,

Daniel He