cancel
Showing results for
Did you mean:
Helper I

## Averaging a count by weekday and hour

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:

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

1 ACCEPTED SOLUTION
Community Support

Hi @nhmpp ,

(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.

2 REPLIES 2
Community Support

Hi @nhmpp ,

(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.

Helper I

Thank you! This worked perfect!

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors