Total percentage of time spent per phase

[TM1992]

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. 

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: 

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

[v-rzhou-msft · Accepted solution]

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)

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.

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. 

[TM1992]

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

Regards,

Thomas