Forum Discussion

rsbin's avatar
rsbin
Community Champion
3 years ago
Solved

Help with AVERAGEX

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,

Need Help
  • bcdobbs's avatar
    bcdobbs
    3 years ago

    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.

4 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    3 years ago

    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.

    • bcdobbs's avatar
      bcdobbs
      Community Champion
      3 years ago

      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.

    • rsbin's avatar
      rsbin
      Community Champion
      3 years ago

      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's avatar
        bcdobbs
        Community Champion
        3 years ago

        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.