Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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.
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 ,
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |