Forum Discussion

stalerik's avatar
stalerik
Icon for Helper II rankHelper II
5 years ago

Summarize entire table into Hour Averages

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 enti...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
stalerik
Icon for Helper II rankHelper II
5 years ago

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]))
)