Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Average of Measure

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 hour...
General Comment
Interesting Links
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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's avatar
    Anonymous
    7 years ago

    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's avatar
v-danhe-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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