Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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
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))
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
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.
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))
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
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?
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
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
53 |