cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Average of Count per Hour over a date range using only a specific day of the week

Hello,

I'm having trouble with the DAX formula to generate the average of the counts over a period of time.

As you can see by this screenshot, the AVERAGE comes out to be the same as the COUNT.

This is the formula I'm using:

but, I've tried multiple varations of it:

1.

AVERAGEX(
KEEPFILTERS(VALUES('Incident'[OwnedByTeamAssignedTimeHour])),
(COUNTA('Incident'[OwnedByTeamAssignedTimeHour]))
)

2.
CALCULATE (
DIVIDE ( [Total Tickets], DISTINCTCOUNT ( Calendar[WeekID] ) ),
FILTER (
ALL ( calendar ),
calendar[WeekID]
>= MAX ( calendar[weekID] ) - 12
&& calendar[WeekID] <= MAX ( calendar[WeekID] )
),values(calendar[day])
)

3.
AVERAGEX(
SUMMARIZE(
Incident,
Incident[OwnedByTeamAssignedTimeHour],
"Count",
COUNT('Incident'[OwnedByTeamAssignedTimeHour])
),
[Count]
)

They all just produce the COUNT.

Additional info:  I took the DATE/TIME field, and split out the Date and Time, and then I rounded the TIME, so that I could COUNT how many tickets came in during that time, so my data looks like this:

Anyone have an idea of why I'm not able to generate the actual AVERAGE?

RC

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX

## Re: Average of Count per Hour over a date range using only a specific day of the week

OK, so the top visual is correct then? And in the bottom visual, you want to see the AVERAGE per day for each hour for the selected date range in the slicer above? Are those all correct statements?

If those are all correct statements, you could do a COUNTROWS of your calendar table (if your slicer is based upon that) and use that as your denominator.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

12 REPLIES 12
Highlighted
Super User IX

## Re: Average of Count per Hour over a date range using only a specific day of the week

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: Average of Count per Hour over a date range using only a specific day of the week

Greg,

Thanks for the reply, and there's a lot of good info over there, but not quite what I need, I don't think.  It could be that I'm not understanding the functions, correctly, though...

RC

Highlighted
Super User IX

## Re: Average of Count per Hour over a date range using only a specific day of the week

I may not be understanding exactly what you are trying to do, but what AVERAGE are you trying to come up with? Because the AVERAGE of a single day or hour is going to be the same as COUNT for that day or hour obviously. So, are you trying to AVERAGE between multiple days or some kind of cumulative thing?

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: Average of Count per Hour over a date range using only a specific day of the week

Yes, sir. It's the average of all tickets received over the last 12 Mondays during the 7:00 AM hour.  (We may have received 137 tickets total during this time frame.  What was the average number of tickets received each Monday during that hour?).

The dashboard can be filtered, by my Director, to then select the 8am hour, and the last 5 Mondays  OR  the 10am hour, and the last 20 Thursdays, etc... (she actually selects the start and end dates, plus the day of the week she's looking at).

Highlighted
Frequent Visitor

## Re: Average of Count per Hour over a date range using only a specific day of the week

Ok, I think I figured out part of my problem... I wasn't using an expression.  (Your comment, plus something a co-worker mentioned, made it click for me).

This is now the new formula I'm testing (which is now giving me a different number, but it's not the average of the values selected).

Average per OwnedByTeamAssignedTimeHour =
AVERAGEX(
KEEPFILTERS(VALUES('Incident'[OwnedByTeamAssignedTimeHour])),
CALCULATE(COUNTA('Incident'[OwnedByTeamAssignedTimeHour])/(VALUES('Incident'[OwnedByTeamAssignedTimeHour]))
))

Now to try and figure out, the right expression.

Highlighted
Community Support

## Re: Average of Count per Hour over a date range using only a specific day of the week

hi,@rcarrinsp

If you still have the problem on it, Could you please share your sample pbix or some data sample and expected output.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

## Re: Average of Count per Hour over a date range using only a specific day of the week

Hi Lin,

Unfortunately, my company blocks access to all 'cloud' locations due to the nature of our data (HIPAA).   With that in mind, I cannot share any of our data, either, due to some of the HIPAA info exists within our ticketing system.  It would take me too long to re-create fake data.

The expected output is the 'average' of the count of rows (not the data within the row) over a specific period of time.

RC

Highlighted
Super User IX

## Re: Average of Count per Hour over a date range using only a specific day of the week

Sample data doesn't need to recreate things exactly, just get the necessary columns to recreate things. For example, is the essentials of your data like:

Date,Hour

1/1/2018,1

1/2/2018,2

1/3/2018,14

Like that?

The big issue I see here is that it seems like you are using specific dates in your axis. If you want to average Mondays, then you need to use the weekday in your axis and not specific dates. So, if you had the above, you could create a column like:

Weekday = WEEKDAY([Date])

Then use that in your axis and then your formula may work as is.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: Average of Count per Hour over a date range using only a specific day of the week

True, I posted a pic of the date info above, so I thought that would help explain it, but I know it's the same thing as having an actual data sample.

I do have a CALENDAR table, and am using WEEKDAY for the axis, my director just wanted to see the dates (of the days she was looking at), so I had to finagle it to show it to me, too.

The Orange arrow points to where I'm using WEEKDAY.

The red arrows are pointing to what I expect the data to look like...   I hard coded the formula to count the rows, and divide by seven (the number of days I'm filtering to find).

I'm trying a different approach of now trying to find a way to remove the 'hard-coded' seven, and Power BI to use the filter here.