Forum Discussion
Help with AVERAGEX
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 ResultNormally 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.
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.
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.
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,
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.
