Measure to find average count by hour by 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'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.

[Fowmy]

@niko18033 

Try the following measure, replace the table name as per your model

Average M = 

VAR _Hour = SELECTEDVALUE(Table8[Start of Hour])
VAR _Day = SELECTEDVALUE(Table8[Day Name])
VAR _Average = 
    AVERAGEX(
        FILTER(ALL(Table8), Table8[Day Name] = _Day && Table8[Start of Hour] = _Hour ),
        Table8[Count]
    )
VAR _Current = SUM(Table8[Count])

Return

_Current  - _Average

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn