cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ncarr_teco
Regular Visitor

Time Format in a Visualization

Hi! 

I´ve a problem with a report that i´m trying to build. I´m currently using an Table from Access for this report, in this table comes info about how long did it take to finish a series of tasks.

I need to make some visualizations that shows the average time by certain categories. The problem is basicly that if I keep the time format (hh:mm:ss) Power BI don´t allow me to use the Average function. 

The only way that I can use this info is if I transform that format (using Power Query) in alphanumeric (as I show in the screenshot)

Do somebody know if there is a way that in the visualization I could see the time format?  

 

ncarr_teco_1-1623248121360.png

 

 

1 ACCEPTED SOLUTION

Hi @ncarr_teco,

 

You can calculate the average time as:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMgIhJR0lQyB2VIrViVYyMLUCihsbAQWM4IKGpkARoDhQwBih0hIuaALETjCVpmYQQVOIYCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t, #"Call number" = _t, Sector = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Sector"}, {{"Avg_Time", each List.Average([Time]), type nullable time}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Avg_Time", type time}})
in
    #"Changed Type1"

v-xulin-mstf_0-1623646141898.png

You can also create measure as:

Measure = 
CALCULATE(
    FORMAT(AVERAGE('Table'[Time]),"HH:MM:SS"),
    FILTER(
        ALL('Table'),
        'Table'[Sector]=MAX('Table'[Sector])))

v-xulin-mstf_1-1623646285544.png

 

But it is not supported to display time in Y-axis as you expected.

Maybe you can vote here.

 

As a workaround, you can convert the averge time to time duration.

In this case, you can display the time duration in Y-axis.

Please refer this:https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

 

Best Regards,
Link

 

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
mahoneypat
Super User IV
Super User IV

You can use a decimal number data type for your time column, do your average or other math, and then just use a custom format string to display it as a time/duration.  Or you can wrap your AVERAGE with FORMAT( ).

Use custom format strings in Power BI Desktop - Power BI | Microsoft Docs

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


v-xulin-mstf
Community Support
Community Support

Hi @ncarr_teco

 

Maybe you can manually calculate average as:

v-xulin-mstf_0-1623401166471.png

Then create column as:

 

Column = 
FORMAT(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE('Table'[avg_hour],":"),'Table'[avg_min]),":"),'Table'[avg_sec]),"HH:MM:SS")

As workaround, you can calculate the average first and then use text format to represent value in time format.

 

The pbix is attached.

 

 

Best Regards,
Link

 

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

First of all thank you very much for your help! I've tried to use your solution and it doesn´t work for what I need to do with Power BI. 

I´ve tried also to use your table in PBI to make a visualization and it doesn´t work the way I need it to.

Just to show you what I really need to do I had use your table in Excel  and add one column with the data of the sector that have made that call (and add also more lines)... The final result is this:

 

ncarr_teco_0-1623524027556.png

I need that PBI could do exactly the same... but there is a problem with the time format/duration. As if PBI dont recognize this. 

 

 

Hi @ncarr_teco,

 

You can calculate the average time as:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMgIhJR0lQyB2VIrViVYyMLUCihsbAQWM4IKGpkARoDhQwBih0hIuaALETjCVpmYQQVOIYCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t, #"Call number" = _t, Sector = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Sector"}, {{"Avg_Time", each List.Average([Time]), type nullable time}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Avg_Time", type time}})
in
    #"Changed Type1"

v-xulin-mstf_0-1623646141898.png

You can also create measure as:

Measure = 
CALCULATE(
    FORMAT(AVERAGE('Table'[Time]),"HH:MM:SS"),
    FILTER(
        ALL('Table'),
        'Table'[Sector]=MAX('Table'[Sector])))

v-xulin-mstf_1-1623646285544.png

 

But it is not supported to display time in Y-axis as you expected.

Maybe you can vote here.

 

As a workaround, you can convert the averge time to time duration.

In this case, you can display the time duration in Y-axis.

Please refer this:https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

 

Best Regards,
Link

 

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

Thank you very much for your help but it didn't work for my visualization. I've tried to split the time field  in three columns and put each one of these fields in a Smart Card.... and it didn't go very well.

I will mark your post as a solution hoping that Microsoft allows us to use te duration format in a visualization. 
PD: I've allready vote!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors