Display both hour and weekday on x-axis with data from 2 different tables

[madeline]

I have 2 tables: Table H shows the historical average of successful logons each hour during the week and Table C shows the current data regarding successful logons for this week. Both tables have columns for day of the week, hour, and number of events. I was able to display the data like this, where the Day was the axis and the Hour was the legend.

However, I would really like for the times to be on the x-axis above the day of the week. Also, if it's possible, I would like to combine the 2 graphs together so I can compare current data side by side with the historical average, but I really have no clue on how to go foward to implement that.

Table H(istorical)

Day	        Hour	EventCounts
Monday	        21:00	7
Monday	        22:00	5
Monday	        23:00	9
Sunday	        16:00	3
Sunday	        17:00	7
Sunday	        18:00	6
Sunday	        19:00	5
Sunday         	20:00	6
Sunday	        21:00	6
Sunday         	22:00	9
Sunday	        23:00	3
Thursday	20:00	4
Thursday	21:00	11
Thursday	22:00	14
Thursday	23:00	9
Wednesday	16:00	20
Wednesday	17:00	11
Wednesday	18:00	12
Wednesday	19:00	12
Wednesday	20:00	6
Wednesday	21:00	13
Wednesday	22:00	7
Wednesday	23:00	14

Table C(urrent)

Day	        Hour	EventCounts
Saturday	13:00	7
Saturday	14:00	11
Saturday	15:00	8
Saturday	16:00	19
Saturday	17:00	5
Saturday	18:00	15
Saturday	19:00	15
Saturday	20:00	2
Saturday	21:00	13
Saturday	22:00	5
Saturday	23:00	16
Sunday	        13:00	2
Sunday	        14:00	60
Sunday	        15:00	70
Sunday	        16:00	81
Sunday	        17:00	28
Sunday	        18:00	18
Sunday	        19:00	3
Sunday	        20:00	17
Sunday         	21:00	15
Sunday	        22:00	1
Sunday	        23:00	18
Thursday	20:00	1
Thursday	21:00	5
Thursday	22:00	11
Thursday	23:00	2

[v-yuta-msft · Accepted solution]

Hi madeline,

To achieve what you want, drag [Hour] column to Axis field and you will ahcieve this:

 

In addtion, to show data in both tables in the same chart, you should build a relationship between two 'Historical' and 'Current' based on some columns. However, in the two tables you have given, there's no relationship between them, to merge them, you can create a calendar column in the two tables.

Regards,

Jimmy Tao

[madeline]

Hi Jimmy,

Thanks for the reply! However, whenever I place the Hour column underneath the Day column, I get this result. 

How do I get to your result?

[v-yuta-msft]

Hi madeline,

Click "Drill Down" button in the left-up corner in stack chart.

 

Regards,

Jimmy Tao

[madeline]

Thank you!