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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors