Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Community. I'm trying to make a scatter chart showing the employees in the X axis and the hours of the day in the Y axis so I can show the hour of arrival of each employee. I have a database like this:
I have created a Date table and an Hour table but for some reason, Power Bi won't let me put the hours in the Y axis. This is my hour table:
Adn this are my relationships:
I would really appreciate some help on this matter. Thanks in advance!
Solved! Go to Solution.
HI, @carlosflores
For the values on the Y-axis have to be aggregated, you need to add a "type" for your basic data to classify these Time.
for example:
assume that:
A representative When they get in.
B representative When they go to lunch
C representative When they come back from lunch
D representative When they leave for the day.
then drag the field type into Legend values
Result:
here is pbix, please try it.
https://www.dropbox.com/s/9rm7g2gpsivty6p/Scatter%20chart%20with%20hours%20as%20Y%20axis02.pbix?dl=0
Best Regards,
Lin
@v-lili6-msft, thaks for your response and sorry for the late reply,
It works but I haven't mentioned an important part of the problem. There are 4 times a day when people record attendance:
1. When they get in.
2. When they go to lunch
3. When they come back from lunch
4. When they leave for the day.
People should get in before 8:30, should go to lunch at 1, should get back from lunch at 2 and leave for the day at 18:00.
I'm using your method but I only see 1 dot per person, when there should be 4 per person (sometimes they don't mark the time, so there should be less dots for that person).
This is my data table
This is how it looks using your method:
As you can see, there's only one dot per person.
Thans again!
HI, @carlosflores
For the values on the Y-axis have to be aggregated, you need to add a "type" for your basic data to classify these Time.
for example:
assume that:
A representative When they get in.
B representative When they go to lunch
C representative When they come back from lunch
D representative When they leave for the day.
then drag the field type into Legend values
Result:
here is pbix, please try it.
https://www.dropbox.com/s/9rm7g2gpsivty6p/Scatter%20chart%20with%20hours%20as%20Y%20axis02.pbix?dl=0
Best Regards,
Lin
hi, @carlosflores
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Best Regards,
Lin
hi,@carlosflores
After my research, field which is not number couldn't be drag into Y axis in the scatter chart.
But you can achieve the requirement by this way:
Step1:
select time column and then click Add column->From date&time->Time->Hour->Hour in Query Edit
you can also do it by dax
Column = HOUR(Table1[Time])
also
then create the relationship by hour
then drag hour into Y aix and set the aggregate type is Average
Result:
Best Regards,
Lin
Hello @v-lili6-msft, I did it exactly like you did and it won't work. Here's how it looks:
I can't add the field with the values of the data source
What I'm trying to do with this report is to show who is getting in late, the hour of entrance is 08:30 and we leave at 18:30. This is being done manually and it takes a lot of time. We'd like to automatize this procedure. Please help!
Thanks in advance
HI, @carlosflores
For your requirement, you can just do like these as below:
Step 1:
use this formula to add a column
attend time = HOUR(Table1[Time])*100+MINUTE(Table1[Time])
Step 2:
Do not create any relationship with time fact table, drag this field into Y-axis and set the aggregate type is Average.
Step 3:
set the format of Y-axis start at 600 and end at 2000
Step4:
you can also add two Y-Axis Constant line as the hour of entrance is 08:30 and we leave at 18:30.
Result:
here is pbix, please try it.
https://www.dropbox.com/s/n3cj47ymirmju55/Scatter%20chart%20with%20hours%20as%20Y%20axis.pbix?dl=0
Best Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |