cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gmarkc Frequent Visitor
Frequent Visitor

Display duration as HH:MM:SS on Y-axis

I need to display a certain duration column (brought in to PBI as a duration type and correctly converted to decimal on load) on the y-axis with a format of "HH:MMSmiley FrustratedS". The value shown will be the average of the duration over various time intervals (Year, Quarter, etc...) I've tried several suggestions found on this forum and elsewhere to no avail.  Here's a screenshot of what I have:

 

 

TAT.png

 

Thanks. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Display duration as HH:MM:SS on Y-axis

Power BI does not have a true duration type. However, I would think that you could create a measure that did your calculation and then convert that to your desired format using the technique here:

 

http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Display duration as HH:MM:SS on Y-axis

Hi @gmarkc,

 

As smoupre said, currently power bi desktop not support display duration at Y-axis. smoupre's solution seems well, you can try it on your side. For your requirement, you can also vote this idea below:

field of "duration" type

 

In addition, you can take a look at below method(convert the time format to Numeric):

 

Table:

Capture.PNG

 

Measures:

Time(WholeNumber) = HOUR(MAX([Date]))*10000+MINUTE(MAX([Date]))*100+SECOND(MAX([Date])) 

Time(Decimal) = HOUR(MAX([Date]))+MINUTE(MAX([Date]))/100+SECOND(MAX([Date]))/10000

 

Notice: When I use calculate column to show the value, I found the values will auto summaried on the visual, so I use measures to instead.

 

Create visual.

Capture2.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
9 REPLIES 9
Super User
Super User

Re: Display duration as HH:MM:SS on Y-axis

Power BI does not have a true duration type. However, I would think that you could create a measure that did your calculation and then convert that to your desired format using the technique here:

 

http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


gmarkc Frequent Visitor
Frequent Visitor

Re: Display duration as HH:MM:SS on Y-axis

Thanks for the quick reply. Sorry, being relatively new to PBI I'm going to need a little more detail to do what you suggested.

 

I'm not sure what you mean by, "...create a measure that did your calculation... ". I already have a column with the duration decimal value and I drag that into the Value well and choose Average as the function. Also, not sure how to use the Duration formula from the link you provided to help convert to my desired format. Can you provide a little more insight?

 

Thanks.

Community Support Team
Community Support Team

Re: Display duration as HH:MM:SS on Y-axis

Hi @gmarkc,

 

As smoupre said, currently power bi desktop not support display duration at Y-axis. smoupre's solution seems well, you can try it on your side. For your requirement, you can also vote this idea below:

field of "duration" type

 

In addition, you can take a look at below method(convert the time format to Numeric):

 

Table:

Capture.PNG

 

Measures:

Time(WholeNumber) = HOUR(MAX([Date]))*10000+MINUTE(MAX([Date]))*100+SECOND(MAX([Date])) 

Time(Decimal) = HOUR(MAX([Date]))+MINUTE(MAX([Date]))/100+SECOND(MAX([Date]))/10000

 

Notice: When I use calculate column to show the value, I found the values will auto summaried on the visual, so I use measures to instead.

 

Create visual.

Capture2.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
ShrikantKhanna Regular Visitor
Regular Visitor

Re: Display duration as HH:MM:SS on Y-axis

Hi @Greg_Deckler,

 

I have gone through your link, it is a great write. I tried it but it does not help in my purpose.

My scenario - I have to not only publish total talk time for the day, I have to aggregate it for the week, month or selected timerange and be able to aggregate for respective teams. 

 

Calculated column Talk Time = CallTimes[Duration]/86400 to calculate duration equivalent to decimal value of HH:MMSmiley FrustratedS and used formatting in PBI desktop.

 

duration.jpgtalktime.jpg

I then create a measure Call Time = SUM(CallTimes[Talk Time]) to aggregate it. Values appear as follows -

appears as.jpg

 

PBI does not have functionality to change format of this calculated TIME in visuals. Other than the table I have to utilize this calculated time in graph to show how many calls/call time happened for days of the week to show a trending.

Please let me know what should I be doing.

Super User
Super User

Re: Display duration as HH:MM:SS on Y-axis

Try multiplying your Call Time by 1. (decimal point included). You should then have the ability to switch the format in the data model to Whole Number, Decimal Number, etc.

 

Call Time = SUM(CallTimes[Talk Time]) * 1.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


ShrikantKhanna Regular Visitor
Regular Visitor

Re: Display duration as HH:MM:SS on Y-axis

Hi @Greg_Deckler,

 

I tried but it does not give me adequate options.

options.png

 

 

 

 

ShrikantKhanna Regular Visitor
Regular Visitor

Re: Display duration as HH:MM:SS on Y-axis

Hi @Greg_Deckler@v-shex-msft

 

Just writing back to know if you have any something for me?

 

Thanks!

 

 

ShrikantKhanna Regular Visitor
Regular Visitor

Re: Display duration as HH:MM:SS on Y-axis

Hi @Greg_Deckler

 

I did the following to serve my purpose -

I broke down calculation to Hours, minutes and seconds and then represent in format to be able to serve my purpose.

 

MEASURES - 

CALLTIMES = SUM(DURATIONINSECONDS)

HRS = TRUNC([CALLTIMES]/3600)

MINS = TRUNC((([CALLTIMES]/3600)-[HRS])*60,0)

SECS = TRUNC((((([CALLTIMES]/3600)-TRUNC([CALLTIMES]/3600))*60)-[MINS])*60)

TIMING = IF(LEN([HRS])=1,"0"&[HRS],[HRS])&":"&IF(LEN([MINS])=1,"0"&[MINS],[MINS])&":"&IF(LEN([SECS])=1,"0"&[SECS],[SECS])

 

This works perfectly for me for any selected time range, whether it is a day/month or months/week or weeks.

mpfarhat Frequent Visitor
Frequent Visitor

Re: Display duration as HH:MM:SS on Y-axis

Were you able to display the HH : MM : SS format on the Y-axis?

 

If so, would you please show an example screen shot of the visualization, and explain how you accomplished displaying this format on the Y-axis?

 

Thanks for any help!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 130 members 1,495 guests
Please welcome our newest community members: