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 We...
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.

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.