Line and Clustered Column charts with calculated (Sumx??) measures - need help

[byronp]

Good Morning, 

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] )

RETURN

CALCULATE (

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)

I did do a dummy pbix file but i cant upload it 

any help would be appreciated 

Cheers, 

[byronp · Accepted solution]

Figured it out, Was just 

ClientRatioSum =

VAR __hour = MAX ( 'Hour'[Time] )

RETURN

CALCULATE (

SUM( 'MergedTable'[ClientRatio] ),

__hour >= 'MergedTable'[StartTime],

__hour <= 'MergedTable'[EndTime]

)

Didnt realise i could just use sum instead of SUMX

[v-luwang-msft]

Hi @byronp ,

You can share your pbix through the public link.

Best Regards

Lucien

[lbendlin]

Paste the sample data into a table in your post or use one of the file services.