cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
byronp
Frequent Visitor

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

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. 

byronp_0-1634511842977.png

 

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. 
byronp_1-1634511972636.png

 

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)

byronp_2-1634512200618.png

 

 

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

 

any help would be appreciated 

 

Cheers, 

 

1 ACCEPTED SOLUTION
byronp
Frequent Visitor

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

View solution in original post

3 REPLIES 3
byronp
Frequent Visitor

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

View solution in original post

v-luwang-msft
Community Support
Community Support

Hi @byronp ,

You can share your pbix through the public link.

 

Best Regards

Lucien

lbendlin
Super User
Super User

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.