cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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,

1 ACCEPTED SOLUTION
Super User
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'
)
)

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

2 REPLIES 2
Frequent Visitor

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

Super User
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'
)
)

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors