How to calculate the average of a count by category

[neiluj]

Hi,

I have a dataset in which one record represents one sale. I have a datetime column and a calculated column 'hour' based on the datetime column. Let's say:

ID	Date		Hour	Quantity
1	2022-08-20 09:00:00		9	2
2	2022-08-20 09:30:00		9	3
3	2022-08-20 09:40:00		9	4
4	2022-08-23 09:00:00		9	2
5	2022-08-24 09:00:00		9	4
6	2022-08-24 9:20:00		9	6
7	2022-08-24 12:10:00		12	3

I also have a measure 'Number of sales' (calculated by taking the count of ID).

I would like to create 2 additional measures: 

• The average of sales (number of records) we get per hour each day (in this example (3+1+2)/3 = 2 between 9 and 10, and 1 bewteen 12 and 13).
• The average of items sold per hour each day (in this example ((2+3+4) +(2) +(4+6))/3 = 7 between 9 and 10).

So that I could get a graph with on the x-axis the hours (from 0 to 24) and as values the above mentioned averages.

Hoping somebody could help

Best regards

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pibx file.

I suggest having a time table like the below.

Avg number of records: = 
AVERAGEX (
    ALL ( 'Calendar'[Date] ),
    CALCULATE ( COUNTROWS ( VALUES ( Data[ID] ) ) )
)

Avg number of Qty: = 
AVERAGEX (
    ALL ( 'Calendar'[Date] ),
    CALCULATE ( SUM(Data[Quantity] ) )
)