Summarize entire table into Hour Averages

[stalerik]

Hello,

I have a Table1 of minute-data with many columns.  I am looking for a way to create Table2 that summarize the entire Table1 into Hour Averages.   Is there an easy way to summarize the entire table into hour averages without having to define each column of the summarized table?  I would like to do this so I can then write measures that can calculate the minimum, maximum, average, etc, of the hour averages over a specific date range.  Thank you!

[amitchandak]

@stalerik , Try a create a new column in table or during aggregation and use that in group by

new Date = date(year([datetime]),month([datetime]),day([datetime])) + Time(hour([datetime]),0,0)

you can use this in measure like

measure = averagex(values(Table[New Date]), calculate(sum(Table[Value])))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[stalerik]

@amitchandak Thank you for this suggestion.  I used your guidance and added a filter to confine it between date1 and date2 but it is not doing what I think it should do.  It should be finding the minimum hour average of the minute-data between the date range but it's finding the minimum of the minute-data.  What I would like to do is to find the minimum of the hour averages, and I thought summarize table would have to be used in there somewhere.

VAR minimum = MINX(
     FILTER(ALLSELECTED('table1'),'table1'[datehour] >= date1 && 'table1'[datehour] < date2),
     CALCULATE(SUM('table2'[value]))
)