Data Consolidation - Combine Timesheet Data

smather · ‎06-25-2018

Hi all

Totally new to this and have no idea what I'm doing, so if anyone could help that would be great.

Trying to consolidate data from timesheets -- they have five tasks per day that range from annual leave, on call, training, etc - and there's about thirty people filling this in; it runs from 1st Jan to 31st Dec.

What I need to be able to do is filter on the month but have each task (of which they can have multipule per day) and the time allocated to that task merge into one, rather than there being several instances of annual leave, for example, have it be just one annual leave for all of them and then in a pie chart, have it show as a percentage of time that's been allocated to that task for the month alongside the percentages against the other tasks. And to then also have one set up to show each persons (or group of people) time against certain tasks, as a percentage of utilization.

This is what I'm working with, with several entries per day by several people:

And here's what I'd like to get out of it (currently done with Excel)

Thank you! 🙂

smather · ‎06-26-2018

Hi @v-juanli-msft

Is there any chance you could help me out with the formula / how you go about doing that? Or if you know of some help online, point me in the right direction?

Many thanks

v-juanli-msft · ‎06-27-2018

Hi @smather

Could you show me some data example and what result you want?

For axample,

calculating the total time allocated to each task = 
CALCULATE(SUM(Sheet1[time]),ALLEXCEPT(Sheet1,Sheet1[tasks]))

Best Regards

Maggie

smather · ‎06-27-2018

Hi @v-juanli-msft

this is the data I've got -- some of the tasks (like install) have a job reference allocated to it, so eventually I'd like to be able to sort on how many hours per task type was spent on a job, but I'm not even over this hurdle yet -- thanks for the hand holding.

This is how the data comes through, I'm not sure why task 2 and task 3 for example have blank things at the top with time associated with them, because it's not like that on the timesheet.

Whenever I try to put them together, it seems to sort on task 1 and then task 2 on how many times it's shown up on task 1? I don't know what's going on there. I'm not sure why Task 3 to 5 aren't grouping together either.

Many thanks 🙂

v-juanli-msft · ‎06-25-2018

Hi @smather

“have each task (of which they can have multipule per day) and the time allocated to that task merge into one”

Calculate the total time allocated to each task

"have it show as a percentage of time that's been allocated to that task for the month alongside the percentages against the other tasks"

Calculate the time of each range (annual leave, on call, training, etc) for each task,

For example, the percentage should be(in a month level)

Total time of annual leave for task1/ Total time of annual leave for all tasks

"And to then also have one set up to show each persons (or group of people) time against certain tasks, as a percentage of utilization"

Percentage:

Total time a person taking/ total time of certain tasks which the person takes part in

How does the dataset including all columns and tables will be applied above looks like?

Best Regards

Maggie

smather · ‎06-26-2018

Hi @v-juanli-msft

Thanks for replying! 🙂

Apologies for sounding stupid, but I'm still in the playing around stages of Power BI -- how would I go about calculating the total time allocated to each task in Power BI, or is that something best done in Excel first?

Many thanks