15 min roll up to hour

[hopeless]

Help!

I'm using PowerBI RS (May 2023) with DirectQuery to Avaya CCMS database (odbc conncetion)

I have 15 minute interval data that I'm trying to roll up to hourly data.  I need to create an average talktime column which is calculated as [talktime] / [callsAnswered].

I can do this for 15 min intervals but I can't get it to roll up to the hour as I can only seem to have it calculate an average of an average (i.e. calculates the talktime/callsanswered for each row then sums the total together, this could be 4+ rows for every skillset every hour depending on how the data was recorded by the phone system. Since is over multiple skillsets so there will be multiple rows with the same date and time but different skill name for the row.

I'd toyed with the idea of making a concatenated column with hour, skillset & date as an identifier for the skillset and the day and using that as a filter for CALCULATE where a concatenated expression of the same values had to equal the concatenated column but I don't know if that would work.EDIT it didn't.

Here's a screenshot showing the layout I'm going for, the next column in the table needs to be a calculation of the filtered data on the visual's row.

Here's a sample from the dataset

[d_gosbell · Accepted solution]

From your description it sounds like you are trying to add this calculation as a calculated column. Calculated columns are always calculated on a row by row basis.

I think the fix for your issue is to create a measure with an expression somehting like the following

DIVIDE( SUM( 'table'[talktime] ) , SUM( 'table'[callsAnswered] ) )

Using the divide function will avoid getting a divide by zero error for the 7am hour. This calculation should then work at the grain of the visual so if you put days instead of hours on the rows it should still work.

[hopeless]

This worked! I'd initially tried creating a measure but I'd made an error in the syntax, thank you!