Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tiagotas
Helper I
Helper I

How to show average hours on graph

I am not able to average the hours shown in the table, nor to show the results of the hours in the chart. How do I create the average hour?

Planilha.PNGTabela.PNG

 

 

The Production Amount is correct, but the hours do not appear and I can not make the hour average!

13 REPLIES 13

What are you expecting to see?



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

I need to create the average hour.

I am unable to display the hour and minutes on the power BI chart as per the information in the spreadsheet above.

I ma happy to help you but you need to provide more Information. I'm sure you know what you need but it is not obvious from the information you have provided. What is the tempo column?  Is it time of day or total hours?  Given there is only 1 record per day, what is the average supposed to be?  Please provide a sample of the result you need. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

First: I need to display the daily hours of the TEMPO column.

 

Second: And according to the column in the image above, I need to average those hours and present the average on the chart.

 

Sorry if I'm not being clear.

I am sure you are trying to be clear, but it is not clear to me.  How about you create a chart in Excel using the data you have posted above so it is clear what you want.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Great suggestion!

I could have thought of it that way before.

01.png

2.png

Unfortuanltey you cannot use time as measure, to achieve this, convert time to number and then use it. See image below, let me know if you need help on how to do this?

 

tim1.PNG



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Yes! I need to know how!

I tried to put it as duration but it did not work out.

duração.png

 

That way that presented in the image is the form that I need.

This is what I did, not added duration but converted time to number. Assuming your tempo field is of datetime type.

 

- click Add Column

- if DataType of tempo field is datetime then add this formula for custom field -> DateTime.ToText([Tempo], "HH.mm") 

- if DataType of tempo field is Time then add this formula for customer field -> Time.ToText([Tempo], "HH.mm")

- Change this new custom field to fdecimal number

 

Now add this new custom field in your bar graph as value.

 

Also make sure in above formula, there is dot (.) between HH and mm. Try it. 

If you need further help, I can send you pbix file, send me a private message with your email in it.

 

Thanks,



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Here is a working solution for you.  I created a measure that respects the current filter context and displays an average hours line.  I had to convert the hours to a decimal number first 

 

avg hours.png

 

here is the sample workbook https://www.dropbox.com/s/v8y46cw5bixolam/average%20hours.pbix?dl=1

 

Here is my measure

Average = CALCULATE(sum(Data[Decimal Hours])/DISTINCTCOUNT(Data[Date]),filter(all(Data[Date]),Data[Date] >=FIRSTDATE(Data[Date]) && Data[Date] <=LASTDATE(Data[Date])))

 

Here is the hour conversion (PQFL)

= Table.AddColumn(#"Extracted Time", "Decimal Hours", each Time.Hour([hour]) + Time.Minute([hour])/60)

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

I could not do the measurement, can you send me the file?

 

Or show me how your table is?


@tiagotas wrote:

I could not do the measurement, can you send me the file?

 

Or show me how your table is?


The file is linked in my earlier post. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

@tiagotas

 

I think this video helps you.

 

 

abs

eduardo

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.