cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rcarrinsp Frequent Visitor
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.

 

Dashboard.jpg

 

 This is the formula I'm using:

 

Average Formula.jpg

 

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:

 

DATE SAMPLES.jpg

 

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

 

Thanks in advance for your help!

 

RC

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


12 REPLIES 12
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


rcarrinsp Frequent Visitor
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

Super User
Super User

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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


rcarrinsp Frequent Visitor
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).

 

 

 

rcarrinsp Frequent Visitor
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. 

Community Support Team
Community Support Team

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.

You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

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.
rcarrinsp Frequent Visitor
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

 

Super User
Super User

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


rcarrinsp Frequent Visitor
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.

 

Weekday.jpg

 

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.