Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
UserID | Group | StartTime | EndTime |
1 | A | 8:00:00 AM | 3:00:00 PM |
2 | A | 9:00:00 AM | 4:00:00 PM |
3 | A | 10:00:00 AM | 5:00:00 PM |
4 | A | 9:30:00 AM | 3:00:00 PM |
5 | B | 7:00:00 AM | 3:00:00 PM |
6 | B | 9:00:00 AM | 3:00:00 PM |
7 | B | 9:00:00 AM | 4:00:00 PM |
8 | B | 9:30:00 AM | 5: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:
How do i go about presenting this in a graph for my dashboard?
Any help would be greatly appreciated
Solved! Go to Solution.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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 what makes you think it is not getting represented? What you are expecting?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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 do you see any issue with the solution I provided. Is something not working?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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 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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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 ?
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
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |