Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
I recently got stuck with this same issue, and option #1 on @v-yuezhe-msft'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 @v-shex-msft on another thread:
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
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.
@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
Hi @v-yuezhe-msft ,
Does pbi already have this feature of displaying time in the y axis?
Thank you so much,
Pedro
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |