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
AaronRogers3
Helper I
Helper I

Power BI Time Calculations

Hi PowerBI,

 

I have been asked to produce some nice visualizations for my end user. Here is some sample data that i have:

 

Cae.PNG

As you can see, my data mainly consists of working with times (these have come from stopwatches).

I am quite a beginner and i am unsure how to create or work with these times.

 

Could anyone suggest how i can get the best out of this data and produce nice visualizations.

 

Something like averages, totals for each etc?

 

Thanks!

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @AaronRogers3,

 

As the picture you  shared, I think the time are all in text format. Here I made a sample for your reference.

 

1. Enter the data and change the data type to Duration for the time columns in query editor.

1.png

 

2. Add some measures as below. 

 

 

ave-se = CALCULATE(SUM(Table3[SETUP_TIME]),ALLSELECTED(Table3))/COUNTROWS(ALLSELECTED(Table3))
aver_sele_set = FORMAT(CALCULATE(SUM(Table3[SETUP_TIME]),ALLSELECTED(Table3))/COUNTROWS(ALLSELECTED(Table3)),"hh:mm:ss")
Total time_num = SUM(Table3[SETUP_TIME])+SUM(Table3[PROCESSING_TIME])+SUM(Table3[RECONCILIATION_TIME])
Total_Time = FORMAT(SUM(Table3[SETUP_TIME])+SUM(Table3[PROCESSING_TIME])+SUM(Table3[RECONCILIATION_TIME]),"hh:mm:ss")

 

3. Then we can get a report like below.

 

2.png

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/bzyc11bqjiuktnb/Power%20BI%20Time%20Calculations2.pbix?dl=0

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

Hi @AaronRogers3,

 

As the picture you  shared, I think the time are all in text format. Here I made a sample for your reference.

 

1. Enter the data and change the data type to Duration for the time columns in query editor.

1.png

 

2. Add some measures as below. 

 

 

ave-se = CALCULATE(SUM(Table3[SETUP_TIME]),ALLSELECTED(Table3))/COUNTROWS(ALLSELECTED(Table3))
aver_sele_set = FORMAT(CALCULATE(SUM(Table3[SETUP_TIME]),ALLSELECTED(Table3))/COUNTROWS(ALLSELECTED(Table3)),"hh:mm:ss")
Total time_num = SUM(Table3[SETUP_TIME])+SUM(Table3[PROCESSING_TIME])+SUM(Table3[RECONCILIATION_TIME])
Total_Time = FORMAT(SUM(Table3[SETUP_TIME])+SUM(Table3[PROCESSING_TIME])+SUM(Table3[RECONCILIATION_TIME]),"hh:mm:ss")

 

3. Then we can get a report like below.

 

2.png

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/bzyc11bqjiuktnb/Power%20BI%20Time%20Calculations2.pbix?dl=0

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @v-frfei-msft 

Frank,

 

Thanks! I have made calculated columns that have the average times for each of my stages.

 

I was wondering, is it impossible to make these new columns that i have created reactive?

 

For example, if i click the NOACK option for machine name then they change to the correct data?

 

Thanks

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.