Display time in Y-axis using Power BI clustered column chart

[Sonal28]

I have multiple time for the selected date. I need to show average time for the selected date/ month using clustered column chart.
Average time which will come top of the BAR. X axis will be time axis. And average time will be there on top of the BAR and height accordingly. How to acheive this. please advice. Thanks in advance.

[phersan]

I recently got stuck with this same issue, and option #1 on @Anonymous's reply got me pretty close by converting the times to integers with a format of HHMMSS. (Ex: 9:24:38 AM would equal 92438).

You can reference that original solution here by @Anonymous on another thread:

https://community.fabric.microsoft.com/t5/Desktop/Display-duration-as-HH-MM-SS-on-Y-axis/m-p/88111/highlight/true#M36966

Just wanted to share an update that Power BI rolled out a Dynamic formatting for DAX measures feature, which gets us even closer now, by displaying an integer as HH:MM:SS.

Basically, just follow shex's instructions to create a DAX measure that represents your time as an integer (they used MAX, but I needed an AVERAGE) :

DAX Start Time = (HOUR(AVERAGE(data_table[Start Time]))*10000) + (MINUTE(AVERAGE(data_table[Start Time]))*100) + (SECOND(AVERAGE(data_table[Start Time]))*1)

Next, change the format of your measure to "Dynamic", and you'll get a drop-down option to input a format string for your measure. I chose "00:00:00" as my format (you can include negative and zero formats as well separated by a semi-colon, if you want to be thorough. see below)

Next, create a Line Graph and use your new DAX measure as the Y-Axis. You'll need to format your Y-Axis in the settings of the chart so that the Display Units don't get auto-formatted to thousands (K). Set that option to "None"

Here is what my final Line Graph looks like in my test file:

The only drawback I have noticed so far is that you won't be able to format your time in 12HH format with an AM/PM indicator. I played around with the formatting by making my AM times negative and PM times positive inside my measure, and including the AM and PM indicators in the positive;negative formatting strings (Ex: "00:00:00 PM; 00:00:00 AM") but still no luck, unless I'm missing something simpler. I don't believe you can use date/time format patterns directly within the Dynamic format feature (i.e. just typing "HH:MM:SS" as your format string didn't work for me).

Perhaps someone smarter than me could hack a mathematical conversion or a clever DAX measure to display a 12HH graph correctly because I'm drawing a blank.

Also, it doesn't look like I can attach a sample pbix file, but if it eventually lets me then I will include what I put together.

Hope this helps!

Paulo

[Sonal28]

I need to show above data using clustered column chart.
Average time which will come top of the BAR. X axis will be time axis. And average time will be there on top of the BAR and height accordingly. How to acheive this. please advice. Thanks in advance.

[Anonymous · Accepted solution]

@Sonal28,

Power BI doesn't support time in Y-axis of clustered column chart, there are workarounds for you to create the visual.

1. Use integer to represent the Time and dispaly integer in Y-Axis, there is a similar thread for your reference.
2. Use R visual, please see this similar thread.

Regards,
Lydia

[Anonymous]

Hi @Anonymous , 

Does pbi already have this feature of displaying time in the y axis?

Thank you so much, 

Pedro 

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490