Measure for average count aggregated by hour of day and day of week

[niko18033]

I'm trying to write a DAX measure to find the average count for a given hour on a given weekday. I plan on using this to compare current volume (most recent complete hour) to the expected volume (average for that particular hour on that particular weekday). For example, I'd like to compare volume for 9:00 am on Monday to all of the 9:00 am hours only for Mondays in the dataset. I have columns created already for Start of Hour (Time data type), Hour (Number), Day Name (text), and Day of Week (num).

I've tried writing some variations of ALL and ALLEXCEPT but have not been able to figure it out so far. I need this figure to be fixed as it will compare current hour by the average for that hour on that day of the week.  Any assistance would be much appreciated!

Here is the format of the data. I just put a placeholder formula on Average which divides count by 13, since that's the approximate number of weeks in my data currently.

[v-lionel-msft]

Hi @AlB ,

Please provide reproducible data in tabular form and expected output table.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB]

Hi @niko18033

It would certainly help if you showed the tables in your model and an example with the expected result. I don't understand what the average is calculated on, what field?  You mention average count ?? count of what, rows? What columns do you have in your tables? What relationships?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers