Time in Service per day (or month)

[Zarlot531]

So here's what I want to do in BI. I have a dataset that shows me how long each technician spent on each dispatch, and I also have the hours he worked each day. What I want to do is, by week, or month, maybe even as a parameter, select the time period and have the techs ranked by their on-site time % for that time period. However, as you can see in the attached, when a tech has multiple dispatches in one day, I repeat how many hours overall he worked multiple times. So in the first row of the example below, this tech likely did other services that day but that 8.35 hours would be repeated for that line. 

Now, if I were doing this by day, it's easy. Just take the average of the overall and the sum of the on site and you can divide the average by the sum to get on-site time for that day. But what happens when I want to view this by month? 

[v-jiascu-msft]

Hi @Zarlot531,

How does it look like in the original format? Can you share a dummy sample? The summarize-function-dax could be helpful.

Best Regards,

Dale

[Zarlot531]

Thanks for your response. I think what I'm going to do instead is simply have a grouped query that has every parameter I need already in it (day,month,weekend date) and then just run with this. I know there's probably a way to do this within the PowerBI interface with the raw dataset, but I guess I'm just used to SQL. I will look into that formula you provided though, looks promising. I guess I can do the drilldown as a separate report if something sticks out in the summary matrix (someone has a low time on site % etc.). Thanks again. 

[Zarlot531]

I guess one way to do it is the write a summary query for each way the user may want to see it: day, month, week, etc. But I'd like them to be able to drill down to daily data in each case.