Scatter chart with hours as Y axis

[carlosflores]

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!

[carlosflores]

@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!

[v-lili6-msft · Accepted 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

[carlosflores]

@v-lili6-msft YES! thank you, it works :') 

[v-lili6-msft]

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

[v-lili6-msft]

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

[carlosflores]

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

[v-lili6-msft]

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