Show Time on a line or area chart

[DreDre]

I have a table that is measuring duration based on 2 columns in Date/Time Format, 2 columns that I am generating and a final column as a group

1. Date_Start
2. Date_End
3. Total_Minutes_As_Whole#
4. Total_Minutes_As_Time
5. Schedule

End_Date	Start_Date	Total_Minutes_As_Whole#	Total_Minutes_As_Time	Schedule
5/25/2021 2:48	5/24/2021 10:30	978	16:18	String 1
5/26/2021 2:20	5/25/2021 10:30	950	15:50	String 1
5/26/2021 23:54	5/26/2021 11:30	744	12:24	String 1

(There are rows with no data, but I want to take an average of the rows that do have them based on the grouping by Schedule)

I am taking  Date_End - Date_Start to get my duration(Total_Minutes_As_Whole#) in min using Duration.TotalMinutes in Power Query. That is giving me an intiger value in min that I can just use a calculated column to convert to time format (Total_minutes_As_Time). The problem is that when trying to chart an average of this column I can only use the minute format which ends up as the display units. If I try with the calculated field for Total_Minutes_As_Time I do not get the option to average. 

I am taking an average duration per schedule and comparing them next to each other. The Schedule column is a text value that I am using for my x-axis.

I have 2 questions:

1. Can I use Total_Minutes_As_Whole# as the data, but have the data labels show Total_Minutes_As_Time?
2. Is there a better way to show average time on a chart? 

Thank-you so much for your help working through this!

[asitm]

@DreDre 
As Ailsa mentioned, Power BI doesnt have default options to do what you are asking.

You could achieve this using custom visual. see below image. If this meets what you had in mind, then let me know and I will send you the custom viz file.

You can create this view yourself with our tool PBIVizEdit   https://pbivizedit.com/gallery/ 

With this tool,

•  anyone, irrespective of technical skills, can create their own visuals
• 15 minutes to create a visual from scratch
• opens up many additional attributes to edit (for e.g. labels, tooltips, legends position, etc)

We are in beta testing currently, and hope to get the first general release by October 2021.
Give this a shot and let us know if you face any problem/errors.

For your convenience, I can send you the custom visual file that I already have.

You can use the editor to modify your visual further (some modifications cannot be done in Power BI window and have to be in editor). We have tried to keep it intuitive and user friendly, but for the first time user, some handholding might be needed.

If you need tutorial/guidance to use the editor (we are still limited on help files), I can help with that.

thanks,

AsitM

[DreDre]

@v-yetao1-msft thank-you for following up!

Our data is structured kind of weird, and in order to get the start from another row to populate on the last day we did some manipulation of the data in SQL that leavs the rows in between as blank, but the last row has the  start/end time for each "day" as a whole.

I can get the aggrigate to show as min or hour with a decimal, but I was wondering if there is a way to do an average using data formatted as time grouped together by Schedule. So similar to what you have above but with the value being average time. Every time I try to chart time I get a flat 1 across the x axis.

I ended up using the summarize option to populate a new table with the column combinations I wanted and then calculated the average of the filters I wanted, then just populated the chart using hours as decimals. It is not very clean so I was hoping there was a better way to do this.

ie show:

Avg of Min = 987

Hour Format = 16.45

Time (Desired) = 16:27

Then plot on a visual chart the 16:27 as the data label

[v-yetao1-msft]

Hi @DreDre 

I don’t quite understand what you mean. As your table shows, there are blank rows. Are these blank rows without any data? If there is no data in the blank rows and a column, there is no way to calculate in Desktop.

In addition, you have to enter the chart What is the average value displayed in the chart? Currently there is only one value in the Schedule column, so only one piece of data can be displayed in the chart.

As for the first question you mentioned, I am afraid there is no way to achieve it. It will only display the value data you put in.

Best Regards

Community Support Team _ Ailsa Tao

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