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.