topic Re: Desperately need help! Avg tickets for each hour for selected days of week in a selected date range in DAX Commands and Tips

Desperately need help! Avg tickets for each hour for selected days of week in a selected date range

Anonymous — Thu, 27 Apr 2023 18:33:03 GMT

I have been trying every possible option for days now and I am just going around in circles. I have a Date table, a Time table, and a data table. My data table contains one ticket per row with a timestamp. I have broken out the time stamp into date, time, weekday and hour. I have a day of week slicer that is connected to my date table ('Date'[Day of Week]) and a date slicer that is connected to my date table ('Date[MMYY]).
These are the measures that I am using:
Total Tickets = Countrows('Mytable')
Avg Per Day = AVERAGEX(DISTINCT('MyTable'[Date Created]),Calculate(Countrows('MyTable')/[Count Days]))

Count Days = DISTINCTCOUNT('MyTable'[Date Created])

Hourly Average = AVERAGEX(SUMMARIZE('MyTable', 'MyTable'[Hour],"Hourly Total",COUNTROWS('MyTable')),[Hourly Total])

The Hourly Average is where I am struggling. The hourly average measure is actually giving me the totals per hour for the selected day where I need the hourly AVERAGE of tickets per hour of each day (for the slicer selected day(s) and Month(s).

I tried Hourly Average = AVERAGEX(SUMMARIZE('MyTable', 'MyTable'[Hour],"Hourly Total",COUNTROWS('MyTable')),[Hourly Total])/[Count of Days] and that gives incorrect averages that do not add up.

As a test, I tried manually adding in the count of days. and this works. The averages per hour across the days is correct. So All Saturday and Sundays in Jan, Feb, and March = 25.
Hourly Average = AVERAGEX(SUMMARIZE('MyTable', 'MyTable'[Hour],"Hourly Total",COUNTROWS('MyTable')),[Hourly Total])/25.

Here's what I get in excel and what I am expecting to see -

How do I get this measure to work using the slicer selected days?

Re: Desperately need help! Avg tickets for each hour for selected days of week in a selected date range

Anonymous — Thu, 27 Apr 2023 19:03:46 GMT

I finally got this to work! Posting the solution here in case others have this issue. At least this appears to function as expected.

I changed the Count Days measure to be:

Count of Days=CALCULATE(DISTINCTCOUNT('Date Created'[Date]),all('Date Created'[Date]))

Then my Per hour measure worked:

AVERAGEX(SUMMARIZE('MyTable', 'MyTable'[Hour],"Hourly Total",COUNTROWS('MyTable')),[Hourly Total])/[Count of Days]

Re: Desperately need help! Avg tickets for each hour for selected days of week in a selected date range

tamerj1 — Thu, 27 Apr 2023 19:39:00 GMT

Hi Anonymous

Please try

Hourly Average = AVERAGEX ( SUMMARIZE ( 'MyTable', 'MyTable'[Date Created], 'MyTable'[Hour] ), CALCULATE ( COUNTROWS ( 'MyTable' ) ) )

Re: Desperately need help! Avg tickets for each hour for selected days of week in a selected date range

upen — Sat, 10 Aug 2024 05:27:13 GMT

What is Hourly Total in your measure?

Is it count of hours or sum of hours?