Averaging a count by weekday and hour

[nhmpp]

Hello- I am having some trouble getting an accurate average to show up in a matrix when averaging by weekday and hour from a separate datetime table.

I have query 1 which is lots of rows with starts at datetime and expires at datetime. I have created a matrix like this that shows how many rows are active during the differnet date/hour combinations, and this works great:

It uses a measure I am called "MatchCount" 

MatchCount = CALCULATE(
DISTINCTCOUNT(Query1[order_number_id]), FILTER(Query1, Query1[starts_at_in_time_zone] <= MAX('DateTime'[DateTime]) && Query1[expires_at_in_time_zone] > MAX('DateTime'[DateTime])))

When I try to average what shows here by the different days of the week instead of specific dates, I cannot seem to create any measure that accurately does this. The one I am working with now shows like this:

And you can see the values are way too high and do not appear to be averaging since there are no decimal numbers. I am calling the current measure shown here "Wkdy Avg 2": 

Wkdy Avg 2 = 
CALCULATE(AVERAGEX(SUMMARIZE(DateTime, DateTime[Weekday], DateTime[Time], "Average", 'Query1'[MatchCount]), [Average]), ALLEXCEPT(DateTime, DateTime[Weekday], DateTime[Time]))

Here is a sample file with a very small amount of fake data that matches exactly what I am working with:

https://premiumparking-my.sharepoint.com/:u:/p/nmargavio/EfkzcV1A_1pIgEbN_KvERlQBOf7Ju5L6aI23R89lJWe...

I would appreciate any suggestions! I have tried just about everything I can think of. 

[v-jialluo-msft · Accepted solution]

^{Hi @nhmpp ,}

^{Please follow these steps:}

^{(1) Create a new measure}

MatchCount2 = 
COUNTROWS (
    GENERATE (
        SUMMARIZE (
            'Query1',
            [order_number_id],
            [starts_at_in_time_zone],
            [expires_at_in_time_zone]
        ),
        SUMMARIZE (
            FILTER (
                'DateTime',
                [DateTime] <= [expires_at_in_time_zone]
                    && [DateTime] >= [starts_at_in_time_zone]
            ),
            [DateTime]
        )
    )
)

^{(2)Final output}

^{Best Regards,}

^{Gallen Luo}

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

[nhmpp]

Thank you! This worked perfect!