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
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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!