Forum Discussion

Locc's avatar
Locc
Frequent Visitor
3 years ago

Hourly Average/Average by hour

Hello all,

 

I have data that is recorded every 2-5 minutes. I need to display this data on a line chart but in when displaying even just 12 hours of data I can end up with hundreds of data points and a chart that stretches off the screen.

 

I need show hourly averages for the data points and show this hourly average on the line graph instead of the individual data points. So far my attempts are not getting the results I need.

 

The closest I have gotten is with this measure, but it seems to be giving me an overall average and not an average for each individual average. 

 

Hourly Average = AVERAGEX(SUMMARIZE(TIRES_TQC, TIRES_TQC[CCWRFH1], "TestCount", DISTINCTCOUNT(TIRES_TQC[BARCODE])), [TestCount])

 

It would be a challenge, but if needed I can probably put together some sample data.

Need Help

3 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    3 years ago

    Hello Locc,

     

    1. Create a new column in your data table that extracts the hour from the timestamp:

    Hour = TIME.HOUR([Timestamp])

     

    2. Create a measure that calculates the hourly average based on the "Hour" column:

    Hourly Average = 
CALCULATE(
    AVERAGE(TIRES_TQC[CCWRFH1]),
    ALLEXCEPT(TIRES_TQC, TIRES_TQC[Hour])
)

     

    3. In your line chart, use the "Hour" column as the x-axis (category) and the "Hourly Average" measure as the y-axis (values). This will plot the hourly averages on the line chart.

     

    Should you require further details or assistance please do not hesitate to reach out to me.

    • Locc's avatar
      Locc
      Frequent Visitor
      3 years ago

      Thanks Sahir,

       

      This seems to work, but when filters are applied it is not giving the desired results.

       

      This table should only show the first 5 results, not sure why the others are on the table.

       

      This graph should also only have those first 5 entries on the graph:

      What can I do to keep the filters intact?

       

      Thanks.

    • Locc's avatar
      Locc
      Frequent Visitor
      3 years ago

      I was able to correct my filter error and it is now only showing the relevant filtered data.

       

      Is there a way to not "stack" all of the hours together?

       

      For example, if I have a 3 day date range selected, it groups all the hours together on this graph. Is there a way to have it show like this:

       

      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24