Line and Clustered Column charts with calculated (Sumx??) measures - need help
I have a dataset of employees for a days work, which includes the start and end times of the employees. The dataset also includes the grouping of the employees and a value assigned to the employees.
What I would like to do is have a line and clustered column chart which shows how many employees are available per hours of the day grouped by the user group. Then have a line which represents the sum total of the values of the employees during the times that they are at work. I have managed to achieve the first part of showing which employees are available with the help from this forum.
Basically, Create a time table for the time in 15 minute segments which would make up the x axis and then create a measure to count the number of employees that are available during the times as per below.
Staff Count1 =
VAR__hour = MAX ( 'Hour'[Time] )
COUNTROWS( 'DataSet' ),
__hour >= 'DataSet'[StartTime],
__hour <= 'DataSet'[EndTime]
This works great to give the following result.
But what i would like to do now is add a line to this graph that pretty much does the same thing as the measure above but calculates the uservalues for the times of day that an employee is present grouped by the Usergroup.
im guessing that i would have to do something similar to the satff count measure using SUMX but i can't seem to get the syntax right. Is this the right way to go about this problem or is there another way to add the line to the graph that would sum the employees values by time of day and grouped?
Desired outcome (i drew the line on and its not reflective on the exact sum totals, just a line for visuals)