calculate total average per hour

[veeayyell]

Hey!

I have a goal of showing average completed chats by hour interval and am needing help with some type of average formula that will not only work for each row, but the overall column total. My current formula yields the correct average figures for all rows and avg total of each day of week, EXCEPT the overall total for AM/PM. 

Ideally for the A.M. portion, it should be averaging together (20+21+18)/3, which is 20. Right now it's showing overall average of 10.

Current formula

Avg Chat Per Hour = AVERAGEX('Chat Queue',

DISTINCTCOUNT('Chat Queue'[Chat Name Request])/

(COUNT(CalendarTable[Day of Week])*DISTINCTCOUNT('Chat Queue'[Hour Timekey])))

Thank you in advance for the help 

[tamerj1]

Hi @veeayyell 

please try

Avg Chat Per Hour =
AVERAGEX (
VALUES ( 'Chat Queue'[Time] ),
CALCULATE (
AVERAGEX (
'Chat Queue',
DISTINCTCOUNT ( 'Chat Queue'[Chat Name Request] )
/ (
COUNT ( CalendarTable[Day of Week] )
* DISTINCTCOUNT ( 'Chat Queue'[Hour Timekey] )
)
)
)
)