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

Showing total staff groups over a time period in a line graph.

Hi All, 

 

Strugging to find a solution for this problem i have.  I have two staff groups with just the start time and end time of their shifts. 

 

Example: 

UserIDGroupStartTimeEndTime
1A8:00:00 AM3:00:00 PM
2A9:00:00 AM4:00:00 PM
3A10:00:00 AM5:00:00 PM
4A9:30:00 AM3:00:00 PM
5B7:00:00 AM3:00:00 PM
6B9:00:00 AM3:00:00 PM
7B9:00:00 AM4:00:00 PM
8B9:30:00 AM5:00:00 PM

 

I want to display this in a multi line grpah with the sum total of the staff as the Y axis 

 

Example: 

byronp_0-1633907734993.png

 

 

How do i go about presenting this in a graph for my dashboard?

 

Any help would be greatly appreciated

 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@byronp see the attached solution, basically, you need a table for hours that you will use on the x-axis and a measure to count staff, and then it is all easy.

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

@byronp what makes you think it is not getting represented? What you are expecting?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

The reason behind that could be that lines are overlapping, switch to clustered column chat to check if you see the values for each group.

 

Also if you just reply saying I cannot see the result it doesn't help in anyways to provide a further solution. You have to be more specific and provide more detail.

 

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@byronp do you see any issue with the solution I provided. Is something not working?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





byronp
Frequent Visitor

Yes your solution makes sense, however i cannot recreate the results in my Dashboard. Im not getting the second group in the line graph at all. I have three groups in my results but even if i add a filter for only two of them i still do not get the two lines on the line graph. 

 

 

parry2k
Super User
Super User

@byronp No, you are not creating a relationship. You can always check in my attached file there is no relationship.

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@byronp see the attached solution, basically, you need a table for hours that you will use on the x-axis and a measure to count staff, and then it is all easy.

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

byronp
Frequent Visitor

Thanks Parry2k, 

 

But quick question though. Would i need to have a relationship between the hours table and the original table, and if so what am i connecting the time table too?  do i connect it to the start time or the end time ?

TheoC
Memorable Member
Memorable Member

Hi @byronp 

 

The X-axis is currently is showing a "Time" - did you want the Start Time or End Time of the Shift?  Also, are you using a Time Table in your Dashboard?  If not, I'd recommend using one such as below:

TimeTable =

 

VAR HourTable = SELECTCOLUMNS(GENERATESERIES((0), (23)), "Hour", [Value])

VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Minute", [Value])

VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Second", [Value])

 RETURN

 

ADDCOLUMNS(

CROSSJOIN(HourTable, MinuteTable, SecondsTable),

"Time", TIME([Hour], [Minute], [Second])

)

From https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

 

The last question prior to putting together a solution is your reference to a "Sum Total of Staff" - is this a Sum Total of Staff as individual persons (i.e. by their UserID) or the Staff Group level (i.e. by their Group A or B)?

 

Thanks heaps, 

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

byronp
Frequent Visitor

Thanks for the reply Theo, 

The graph is to show the number of staff that a present at the times of the day so it wouldnt be based on the start time or end time as one of the other. 

 

I would like to group it on the staff group to show how many of that staff group was present at any point in the day 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors