Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Help with creating an hours total chart?

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:

 

Need Help

5 Replies

  • rsbin's avatar
    rsbin
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        rsbin
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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,