cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jamesfulford Frequent Visitor
Frequent Visitor

Timesheet data - visualise specific activity in each month

Hi,

 

I have a dataset of timesheet data, relevant columns are:

 

Entry Date - date of time entry e.g. 26 July 2018

Hours Worked- time spent on an activity - decimal hours

Task User Name - details of a specific actitvity e.g. Administration

MonthYear - custom column showing just hte Month and Year to help order things - August 2018

 

I need to show the time spent on a task (e.g. administration) as a percentage of the total time recorded in a single month.  I think I need to create an additonal column with the subtotal of hours for each month and then can use that to create the percentage in a further column but i cannot work out how to make the subtotal!

 

It also needs to be dynamic as am creating a template that I want to run each month without updating it...

 

Any help appreicated. 

 

Thanks,

 

James

 

5 REPLIES 5
Super User
Super User

Re: Timesheet data - visualise specific activity in each month

Hi @jamesfulford,

 

You need to create a measure to calculate this:

 

hours =
SUM ( Timesheet[Hours Worked] )
    / CALCULATE (
        SUM ( Timesheet[Hours Worked] );
        ALL ( Timesheet[Month Year]; Timesheet[Task User Name] )
    )

Without any data example is difficult to give you a better answer.

 

Chan you share some sample data and expected result?

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




jamesfulford Frequent Visitor
Frequent Visitor

Re: Timesheet data - visualise specific activity in each month

Capture.PNG

 

Hi @MFelix

 

Thanks for replying.

 

Sample data attached. 

 

I don't understand how that measure will help me to find the monthly total for each month and then

allow me to use that in later calculations?

 

Thanks,

 

James

 

jamesfulford Frequent Visitor
Frequent Visitor

Re: Timesheet data - visualise specific activity in each month

Capture1.PNG

I can get the subtotals in a visualisation easily - I just want to use these montly substotals in further calculations!

 

Is there a way to refer to this table or can i create this table dynamically in an expression so i can set up a robust template?

 

Thanks,

 

James

 

jamesfulford Frequent Visitor
Frequent Visitor

Re: Timesheet data - visualise specific activity in each month

MonthHours = CALCULATE(SUM('January 2019 (rolling 6)'[Hours Worked]),RELATED('January 2019 (rolling 6)'[MonthYear]))
 
Trying to use this but there isn't a relationship...
 
James
 
Community Support Team
Community Support Team

Re: Timesheet data - visualise specific activity in each month

@jamesfulford,

 

You may take a look at the following post.

https://community.powerbi.com/t5/Desktop/Subtotal-Percentages/m-p/313658#M139139

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.