Help with AVERAGEX

[rsbin]

Good Afternoon,

Trying to gain a better understanding of the AVERAGEX Function.

My scenario is as follows:  I have WorkOrders completed throughout the day.  My Fact Table includes Date, Day of Week, and Hour.

ID	Hour	Date	Day
1	8	12-Sep	Monday
2	8	12-Sep	Monday
3	9	12-Sep	Monday
4	9	12-Sep	Monday
5	9	12-Sep	Monday
6	10	12-Sep	Monday
7	10	12-Sep	Monday
8	11	12-Sep	Monday
9	12	12-Sep	Monday
10	8	5-Sep	Monday
11	7	5-Sep	Monday
12	9	5-Sep	Monday
13	9	5-Sep	Monday
14	13	5-Sep	Monday
15	14	5-Sep	Monday
16	14	5-Sep	Monday
17	15	5-Sep	Monday

I need to calculate the average of the count of work orders completed by both day of week and hour of the day.

Hoping one of you can help set me straight as to how best to set up my Measure so I can create a Matrix visual similar to this (please disregard the color formatting):

Thanks in advance and Best Regards,

[bcdobbs · Accepted solution]

Try something like this:

Average Count Per Day = 
VAR DayHourGrain =
 ADDCOLUMNS (
    SUMMARIZE ( 
        Table1,
        Table1[Date],
        Table1[Day], 
        Table1[Hour] ),
    "@RowCount", CALCULATE ( COUNTROWS ( Table1 ) )
 )

VAR Result = 
    AVERAGEX (
        DayHourGrain,
        [@RowCount]
    )

RETURN Result

Normally if you just needed an average by day I'd suggest a date table and just use that as the table aspect of AVERAGEX but here you need to build a distinct list of date/day/hour which you can then iterate over.

Might be a more efficient way of doing it but I think this works.

[rsbin]

@bcdobbs ,

Thanks very much for the response Ben.

I was stuck at how best to create the virtual table.  I ended up creating an actual Calculated Table, but I was certain there was a more effective / efficienct way to do this.  I think you got me over the bump I was having.

Thanks again and Best Regards,

[bcdobbs]

I'd be interested to lookat the performance comparison between the two especially if your actual data is very large. Moving the table variable over into a calculated table with a date dimension and an hour dimension for example filtering both effectively would act as a pre aggregated table.

Glad that helped though.

[bcdobbs]

If you need it to calculate an overall average including zero hours you'd need to cross join your table variable so you have every combination of date/hour.