cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors