cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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

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?
1 ACCEPTED SOLUTION
Frequent Visitor

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]

2 REPLIES 2
Super User

Hi @bevawn

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

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]

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors