Average time measure is working at row level, but it is incorrect at the total level

[SHill83]

Hello,

I have created a measure to get the average for a web chat time field for all connected web chats only. The time field is in seconds, so I have used the following measure to turn it into a HH:MM:SS format which all seems to work fine on the 'row' level, but not at the 'total' level - please can someone help? 

My measure is:

Avg. webchat time =

VAR ss =

SUM(  'tablename'[webChatTalkTime])/ CALCULATE (

     COUNT('tablename'[chatResult]),

    'tablename'[chatResult] = 'connected'

)

VAR mm =    

     ( ss - MOD ( ss, 60 ) ) / 60

VAR HH =

     ( mm - MOD ( mm, 60 ) ) / 60

VAR mmm =

    mm - MOD ( mm, 60 )

VAR res = ss - hh * 3600

VAR rem =

     ( res - MOD ( res, 60 ) ) / 60

VAR ress = ss - rem * 60 - hh * 3600

RETURN

    FORMAT(format(hh,"0#") & ":" & format(rem,"0#") & ":" & format(ress,"0#"), "HH:MM:SS")

The outcome for a specific month is as follows:

I believe the average should be 20mins 16 seconds - not 15mins 4secs?

The outcome for another month is:

I believe the average should be 19mins 29secs - not 14mins 50secs?

Can anyone see where i am going wrong?

Many Thanks, 

[SHill83]

Thank you so much for your response, I think this has done the job - thank you! 🙂

[vanessafvg · Accepted solution]

there could be a few things wrong

1. total are very confusing in power bi 

2 or it could be how your model is set up

for avg chat time

do you want this part to also be where it is connected? calculate(SUM(  'tablename'[webChatTalkTime])

  if so you need to change it to

change =
DIVIDE (
CALCULATE (
SUM ( 'tablename'[webChatTalkTime] ),
'tablename'[chatResult] = 'connected'
),
CALCULATE (
COUNT ( 'tablename'[chatResult] ),
'tablename'[chatResult] = 'connected'
)
)