Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to Depict Durations Properly in Visuals

Hello- I've been running into an issue trying to show durations in my visuals. I have the data transformed as duration in Power Query, and then have to change the type to Time, in the table view to get the attached photos. (Note that the data is pulled in as a decimal, when changed to duration it's then a decimal-time hybrid, and finally appears as a duration) I'm wanting to show average durations for my dataset in a line or bar graph. When move this over, the only option I have is to show it as a count.

 

Essentially, I'm looking to show the average amount of time spent in a status code by day, and this is for multiple users. The duration is only being shown as a count, and not average duration in any of the visualizations I'm wanting to use.

 

I've searched all over for any sort of solution and nothing has come up. Any and all advice would be greatly appreciated.

 

Germaican_2-1613138036160.pngGermaican_1-1613137804641.pngGermaican_0-1613137640696.png

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

As I mentioned, declare your durations as decimal numbers and then create a measure:
 
Dur = FORMAT(sum('Duration'[duration]),"hh:mm:ss")
 
lbendlin_1-1614105574968.png

 

 

 
NOTE:  This does not handle durations well that cumulatively exceed 24 hrs.

 

 

View solution in original post

10 REPLIES 10
lbendlin
Super User
Super User

in such a case I would keep the axis data in numeric form, and provide the duration format in the tooltips.

Anonymous
Not applicable

I was able to get the duration to show and it looks fairly accurate. When I attempt to place this in a line chart, that's where I'm unable to get a visual. I have some images showing what I'm seeing and then a snip from Excel that depicts what I'm trying to see in PowerBI.

 

Screenshot 2021-03-03 125446.pngScreenshot 2021-03-03 125853.png

lbendlin
Super User
Super User

As I mentioned, declare your durations as decimal numbers and then create a measure:
 
Dur = FORMAT(sum('Duration'[duration]),"hh:mm:ss")
 
lbendlin_1-1614105574968.png

 

 

 
NOTE:  This does not handle durations well that cumulatively exceed 24 hrs.

 

 
Anonymous
Not applicable

I'm able to format the data as hh:mm:ss, but the issue I'm running into is attempting to place this in a line chart. (I'm only able to use this in a matrix) Are you aware of a way so that this can show durations in a chart to show the trending lines from day over day?

lbendlin
Super User
Super User

Please provide sample data in usable format (not as a picture) and show the expected outcome.

Anonymous
Not applicable

Below is some sample data. Duration is setup as percent of day, in decimal format. When I'm wanting to place this in HH:MM:SS format, I'm unable to have this format appear in visuals, but rather it defaults to 'Count' with no options to change.

 

day teamName agentName idleCodeName duration count
2/12/2021 Transition Response Sample 1 null 0.1434375 72
2/12/2021 Transition Response Sample 2 null 0.251076389 164
2/12/2021 Transition Response Sample 2 Work 0.039930556 15
2/12/2021 Transition Response Sample 3 null 0.271388889 196
2/12/2021 Transition Response Sample 4 null 0.290416667 235
2/12/2021 PM Response Sample 5 Meeting 0.023796296 1
2/12/2021 PM Response Sample 5 Outbound Calls 0.034502315 5
2/12/2021 PM Response Sample 5 null 0.00318287 11

lbendlin
Super User
Super User

Do you want to be able to aggregate the durations?  In that case you probably need to treat them as number (fractions) internally and then produce the desired output via a FORMAT measure.

Anonymous
Not applicable

Could you expand on what you mean by this please? I believe I've tried this step and have still run into the same error.

lbendlin
Super User
Super User

What time unit do your users expect?  Duration in hours, or in minutes?

Anonymous
Not applicable

We have the formatting as HH:MM:SS.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors