How to calculate total hours all employees have worked in a month

[PowerBiNoob37]

Hi,

Im trying to work out from timesheet data how many hours all the employees have worked in a month my data is like so for example

User	Total Hours	Date
Joe Bob	6	1/01/2000
Missy Moo	7	1/01/2000
Joe Bob	4	2/01/2000
Missy Moo	3	2/01/2000
Joe Bob	5	3/01/2000
Missy Moo	7	3/01/2000
Joe Bob	7	1/02/2000
Missy Moo	8	1/02/2000
Joe Bob	9	2/02/2000

Im guessing i need to create a table that looks like the following

Month	Total Hours
Jan-00	32
Feb-00	24

Could some one show me how?

Also i know i can use the auto totals however i need to use the numbers further on in other calculations

[v-angzheng-msft]

Hi, @PowerBiNoob37 

If you want to change the value of the matrix total field, you can refer to the following thread

Different DAX calculations for different row hierarchies in a matrix table? Independent subtotals?

then to create a measure similar to this:

_Switch = 
// VAR _IF=
//     IF(
//         ISINSCOPE(financials[Year])&&ISINSCOPE(financials[Product]),
//         IF(
//             ISINSCOPE(financials[Country]),
//             _VALUE,
//             _SUBTOTAL),
//     _TOTAL)

VAR _SWITCH=
    SWITCH(
        TRUE(),
        ISINSCOPE(financials[Country])&&ISINSCOPE(financials[Product]),1,
        ISINSCOPE(financials[Year])&&ISINSCOPE(financials[Product]),2,
        ISINSCOPE(financials[Year])&& NOT(ISINSCOPE(financials[Product])),3,
        NOT(ISINSCOPE(financials[Year])),4
        )
RETURN _SWITCH

You can change it the way you want.

Result:

Please refer to the attachment below for details. Hope it helps.

If it's not helpful to your case, please consider sharing the sample, it's hard to reproduce your scenario if it's not clear what kind of data model you have.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

No need to show real data, you can always create some short dummy dataset to illustrate your issue.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PowerBiNoob37]

Pretend the numbers here are mathamatically correct i want it too look exactly like this and then i can use a slicer to zone in on the dates..

[ebeery]

@PowerBiNoob37 I would recommend the following:

1. Create a date table (https://docs.microsoft.com/en-us/power-bi/guidance/model-date-tables )
2. Create a "Total Hours" measure like below to use in your visual 

Total Hours (measure) = SUM(Table[Total Hours]​

[PowerBiNoob37]

Ive more or less done this however it isnt working for me,

I created a Date Table like normal

CalanderTable = CALENDARAUTO()

i set up the relationships one to many to the other two columns im currently joining

I then used the measure

SUM(Table[Total Hours]​

But when i use it in my report i cannot add it as a column i.e (its in the values not the column like i want) Whats happening>?

This is why i asked someone to show their method as im stumped

[Ashish_Mathur]

Hi,

In the Formatting pane, under the values group, turn on Show on rows.

[PowerBiNoob37]

That doesnt work it just displays it differently, i think im doing something wrong, like the measures are right it worked out the whole total but it doesnt display the measure for the month i.e the 3,984.45 is the Total Hours all up for every employee for the total time period, where as i want it to measure each month seperatly..

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[PowerBiNoob37]

I cant it contains sensitive data. I think the issue is the measure is wrong for what im trying to achieve

im trying to create this

When my data in one table ill call EmployeeHours is like this

User	Total Hours	Date
Joe Bob	6	1/01/2000
Missy Moo	7	1/01/2000
Joe Bob	4	2/01/2000
Missy Moo	3	2/01/2000
Joe Bob	5	3/01/2000
Missy Moo	7	3/01/2000
Joe Bob	7	1/02/2000
Missy Moo	8	1/02/2000
Joe Bob	9	2/02/2000

and another like this ill call SafetyTotals

Type	date
safety	1/02/2000
safety	1/03/2000
nly	1/01/2000
nly	1/02/2000
int	1/01/2000

I have a table ive created using the following

CalanderTable = CALENDARAUTO()

That i have a one to many relation ship with from the dates in my tables to the calander table (all the dates are in the right format)

and the measure im using that adds them all together is

is

Total Hours Measure = SUM(EmployeeHours[Total Hours])

The data matrix is like so

and if i add the measure i get the table like ive shown before

if change it too your suggestion i get this sort of thing not the one i want

[Ashish_Mathur]

Hi,

In the Calendar Table, create the Year, Month Name and Month number columns.  Sort the Month Name column by the Month Number column.  Drag the Year and Month Name columns to your matrix visual from the Calendar Table.

Hope this helps.