Average Per Hour

[Anonymous]

Hello all,

I need to display in a visual Card the average per hour in total.

I have the following Fact table:

Id	QuantityExecuted	DimStartTime_id
1	20	11:54:51
2	1	11:55:12
3	3	11:55:15
4	4	11:55:18
5	20	11:58:16
6	1	11:58:55
7	3	11:59:02
8	4	11:59:05
9	20	11:59:48
10	1	11:59:55
11	3	11:59:59
12	4	12:00:02
13	20	13:18:51
14	1	13:19:03
15	3	13:19:07
16	4	13:19:10
17	1	15:06:50
18	1	15:07:27
19	1	15:07:39
20	1	15:08:21

And the DimTime table:

Id	Hour	Minute	Second	CompleteTime
00:00:00	0	0	0	1900-01-01 00:00:00.000
00:00:01	0	0	1	1900-01-01 00:00:01.000
00:00:02	0	0	2	1900-01-01 00:00:02.000
00:00:03	0	0	3	1900-01-01 00:00:03.000
00:00:04	0	0	4	1900-01-01 00:00:04.000
00:00:05	0	0	5	1900-01-01 00:00:05.000
00:00:06	0	0	6	1900-01-01 00:00:06.000
00:00:07	0	0	7	1900-01-01 00:00:07.000
00:00:08	0	0	8	1900-01-01 00:00:08.000
00:00:09	0	0	9	1900-01-01 00:00:09.000
00:00:10	0	0	10	1900-01-01 00:00:10.000

This table has all the possible time slots and it continues but for the example I think I don't need to show.

So my main goal here is to calculate the average of the QuantityExecuted per hour but to show it in a Card. 

Is there a way to build a measure that gives me the "total" average by hour.

I know that in a graph I can calculate the average per hour. And I already have that but now I need to calculate in total to show only one value.

Thanks in advance,

Rui

[amitchandak · Accepted solution]

@Anonymous , create hour as time  in your table 

or use hour column

example

averagex(values(Time[Hour]), calculate(sum(Table[Quantity])))

[Anonymous]

Hello @amitchandak ,

Thank you for your reply. It was correct. I found out some example but was not totally correct.

Thanks and have a nice day,

Rui