Skip to main content
cancel
Showing results for 
Search instead 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

Reply
SHill83
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:
SHill83_0-1688488977493.png

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

 

The outcome for another month is:

SHill83_1-1688489269003.png

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

 

Can anyone see where i am going wrong?

 

Many Thanks, 

 

 
1 ACCEPTED SOLUTION
vanessafvg
Super User
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!




View solution in original post

2 REPLIES 2
SHill83
Frequent Visitor

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

vanessafvg
Super User
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!




Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.