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
Dolf
New Member

How to get average time spend on card

I have data from a Service Desk system and building a dashboard for management and need average time. 

i have created this mesure to convert the time to "hh:mm:ss"

First Response Time in Time =
TIME(
    DIVIDE(
        [First Response Time in Bhrs] , 3600000
    ),
    DIVIDE(
        MOD(Total_tickets_data[First Response Time in Bhrs] , 3600000) , 60000
    ),
    DIVIDE(
        MOD(Total_tickets_data[First Response Time in Bhrs] , 60000) , 1000
    )
)
 
However I am unable to get the average on a callout card and need help please?



Screenshot 2024-04-17 121600.pngScreenshot 2024-04-17 121700.pngScreenshot 2024-04-17 121819.png

1 ACCEPTED SOLUTION
v-yohua-msft
Community Support
Community Support

Hi, @Dolf 

Based on your information, i created a table:

vyohuamsft_1-1713841594723.png


Create a new Measure , then use the following DAX:

Average First Response Time = 
VAR AvgTimeInBhrs = AVERAGE('Table'[First Response time])
RETURN
TIME(
    DIVIDE(AvgTimeInBhrs, 3600000),
    DIVIDE(MOD(AvgTimeInBhrs, 3600000), 60000),
    DIVIDE(MOD(AvgTimeInBhrs, 60000), 1000)
)

 

Change its time type to hh:mm:ss

 

vyohuamsft_2-1713841698219.png

 

This measure is then placed in the card view, and when a piece of data is selected, the card will display the corresponding time, the following is my preview:

 

vyohuamsft_0-1713841529994.png

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yohua-msft
Community Support
Community Support

Hi, @Dolf 

Based on your information, i created a table:

vyohuamsft_1-1713841594723.png


Create a new Measure , then use the following DAX:

Average First Response Time = 
VAR AvgTimeInBhrs = AVERAGE('Table'[First Response time])
RETURN
TIME(
    DIVIDE(AvgTimeInBhrs, 3600000),
    DIVIDE(MOD(AvgTimeInBhrs, 3600000), 60000),
    DIVIDE(MOD(AvgTimeInBhrs, 60000), 1000)
)

 

Change its time type to hh:mm:ss

 

vyohuamsft_2-1713841698219.png

 

This measure is then placed in the card view, and when a piece of data is selected, the card will display the corresponding time, the following is my preview:

 

vyohuamsft_0-1713841529994.png

 

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

samratpbi
Solution Supplier
Solution Supplier

Hi, I have created a sample table like below:

samratpbi_0-1713353885188.png

Then created below measure:

Avg Time Spent =
VAR _avgT = AVERAGE(Avg_Time[Time])
VAR _hr = FORMAT(TRUNC(DIVIDE(_avgT,3600)),"00")
VAR _min = FORMAT(TRUNC(DIVIDE(_avgT - _hr*3600,60)),"00")
VAR _sec = FORMAT(TRUNC(_avgT - _hr*3600 - _min*60),"00")
RETURN
_hr & ":" & _min & ":" & _sec
And the result is below:
samratpbi_1-1713353947058.png

I can see the Agent wise average.

If this resolves your problem then please mark this as solution, Thanks!

Now put that on a "Card", it does not work.

Dolf
New Member

I have created this measure:

VAR _avgTime = AVERAGE (Total_tickets_data[First Response Time in Bhrs])

Then used this to convert to time, the problem is your "Card" can not provide an average. How would you get the average per Agent?Screenshot 2024-04-17 130252.png
samratpbi
Solution Supplier
Solution Supplier

Hi,

First you need to have average of all the rows then try to convert into hh:mm:ss. Hence in your measure, create a variable like VAR _avgTime = AVERAGE (column_name). Then try to extract Hour, Min, Sec from there.

If this resolves your problem then please mark it as solution, Thanks!

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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