Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rcarrinsp
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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

12 REPLIES 12
Greg_Deckler
Super User
Super User


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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

 

 

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. 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

 

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Greg,

 

Thanks for your help!  I believe that solved it.  I've been testing it, and it looks good.

 

 

RC

Yes, the top visual is correct, and yes, those are all correct statements.

 

I think that worked!  I'm testing now.  Thank you!  I'll mark it as the answer, once I'm done with my testing.  I appreciate it!!

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.

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.