Help with creating an hours total chart?

[Anonymous]

I have an imported table that contains employee names (memberID) their scheduled hours (Hours) and the date that the hours are scheduled (Date). I also have a Date Table created for "date" relationships. (To break dates into months, weeks, etc etc).

I'm trying to build a chart similar to the screenshot below - that shows each employees sum or hours per week.

1) I'm not sure what chart to pick (if this is even possible)?

2) I have another table (Relationships) that categorizes those hours into several different types (ie "Revenue", "Non-Revenue", "Other"). So I'd like to be able to click on any employee's box (Ie John Doe's '34.75') it will break that 34.75 into the categories (second screen shot)

Any help would be much appreciated.

HIGH LEVEL TABLE I'D LIKE TO REPLICATE (NOTE: COLORING IS JUST BASED ON UTILIZATION. IE >40 HOURS THAT WEEK = BLUE, >30 HOURS, GREEN, ETC ETC)

DRILL DOWN CHART OF JOHN DOE WEEK 03/14 HOURS:

MY DATA:

[rsbin]

@Anonymous ,

The Visual I use for this is the Matrix Visual.

Drag Employee Name into the Rows, and  Week from your Date Table into Columns.  I use Date in my example below.

Bring Scheduled Hours into the values:  (I have additional rows that you do not need)

This is the first step to get working.  Once you get this, then you can move on to conditional formatting.

Hope this gets you going in the right direction

[Anonymous]

Thank you SO much, this helps me quite a bit. If I may, follow-up question - how do you get your week column headers? My date table is defined as below with the available fields in the screen shot to the right.

THANK YOU!

TableDT = 
VAR MinYear = YEAR ( MIN ('CW_Scheduled Hours'[Date] ) )
VAR MaxYear = YEAR ( MAX ('CW_Scheduled Hours'[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Week Number", WEEKNUM ( [Date] ),
"Year Number", YEAR ( [Date] ),
"Index", 12 * YEAR ( [Date] ) + MONTH ( [Date] ))

 

[rsbin]

@Anonymous ,

Create another calculated column and concatenate "Year Number" and "Week Number"

Year-WeekNumber = SWITCH(
                            TRUE(),
                            [Week Number] < 10, [Year Number]  & "-0" & [Week Number],
	                    [Week Number] >= 10, [Year Number]  & "-" & [Week Number] )

I use this to ensure all digits line up correctly.

Glad you are going in the right direction.

Regards,

[Anonymous]

I am very sorry to keep asking questions. Two follow-ups if you don't mind:
1) How does the "TRUE()" fit into the Switch? Why is it there?

2) How would I limit the # of weeks to the following 4 weeks only (4 weeks after the currennt week). Would this be in the filter somehow?

Thank you, again!

Rob

[rsbin]

@Anonymous ,

Good Morning.

1)  Don't have a good answer for you.  Whoever taught me this function, did it this way and has been working for me ever since.   Maybe a youtube video may have a better explanation.

2)  I think using the Filter Pane would be your best bet.  On your Visual Level filter, drag in your Date column and use the Relative Date filter.  Select "is in the next" and then fill in 28 days or whatever you need it to be.

Glad I could help and good luck.

Regards,