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
TM1992
Frequent Visitor

Total percentage of time spent per phase

Hi there,

 

I am trying to create a visual that would show the total percentage of time spent per Test phase. The visualization metrics I have shown below are incorrect and I'm curious how I could solve them. 

 

As you can see, above the visual I have 2 driving examiners (Driving Examiner ID). These examiners have conducted 13 tests on 14/06/2021 all of these times summed up brings a total = 10:01:23. 

 

TM1992_0-1623925011362.png

 

In each Driving test, there are test phases, with a start time and an end time. What I have done is end-time- start time = duration For example: 

 

TM1992_1-1623926587066.png

 

This allows me to understand the duration per phase. 

 

I have attached a link below also to the file, so if anyone could assist that would be much appreciated.

 

https://1drv.ms/u/s!AoTkCNSjWHozjhe0nOmN89jNv00f?e=CyfIWr 

 

Kind regards,

Thomas

 

 

 

 

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

Hi @TM1992 

Power BI doesn't support you to calculate by Time type value directly. Try to transform the duration to hour, minute or second in number type to calculate the result.

My Sample:

I use datediff function to build a second column as below.

Duration Second = DATEDIFF(Sheet15[Start Time],Sheet15[End Time],SECOND)

1.png

And I build a measure duration.

Duration Time = 
VAR _hh = QUOTIENT(SUM(Sheet15[Duration Second]),3600)
VAR _min = QUOTIENT(SUM(Sheet15[Duration Second])-_hh*3600,60)
VAR _sec = SUM(Sheet15[Duration Second]) - _hh*3600 - _min*60
RETURN
_hh&":"&_min&":"&_sec

Result is as below.

2.png

Best Regards,

Rico Zhou

 

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

View solution in original post

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

Hi @TM1992 

Power BI doesn't support you to calculate by Time type value directly. Try to transform the duration to hour, minute or second in number type to calculate the result.

My Sample:

I use datediff function to build a second column as below.

Duration Second = DATEDIFF(Sheet15[Start Time],Sheet15[End Time],SECOND)

1.png

And I build a measure duration.

Duration Time = 
VAR _hh = QUOTIENT(SUM(Sheet15[Duration Second]),3600)
VAR _min = QUOTIENT(SUM(Sheet15[Duration Second])-_hh*3600,60)
VAR _sec = SUM(Sheet15[Duration Second]) - _hh*3600 - _min*60
RETURN
_hh&":"&_min&":"&_sec

Result is as below.

2.png

Best Regards,

Rico Zhou

 

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

Thanks, RicoZhou! This is great, it's exactly what I'm after.

 

Regards,

Thomas

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.