please help to get active time on power bi

[EricoVincentciu]

hello i wanna make total average base on dept and distinct by internal id 

for example when i choose dept

FM-GA

FM-SBB

FM-WHCH

 i got (50 vehicle) dynamic 

if i choose another dept for example 

FG-WH

I got 27 Dynamic


and i wanna count Total hour divide total vehicle base on dept 

in excel i got formula like this : 

- Step one 

sum total hour active 

- step two

sum of total divide vehicle 

- Step 3 

result divide 528

528 is a range between 6 aug until 27 aug multiply ( * ) 24 hour

Last Convert to percentage

this is my raw master data at excel 

internal_id	type	status	unit	operational_date	time_start	time_finish	dept_id	menit	jam
DTOF000004	OPERATIONAL	C	MAIN	06/08/2024	08:05	15:35	FM-GA	0,31	7,50
DTOF000004	OPERATIONAL	C	MAIN	06/08/2024	16:10	23:25	FM-GA	0,30	7,25
DTOF000004	OPERATIONAL	C	MAIN	07/08/2024	08:05	15:20	FM-GA	0,30	7,25
DTOF000004	OPERATIONAL	C	MAIN	07/08/2024	16:15	23:30	FM-GA	0,30	7,25
DTOF000004	OPERATIONAL	C	MAIN	08/08/2024	08:05	13:45	FM-GA	0,24	5,67
DTOF000004	OPERATIONAL	C	MAIN	08/08/2024	16:05	23:30	FM-GA	0,31	7,42
DTOF000004	OPERATIONAL	C	MAIN	09/08/2024	16:15	23:30	FM-GA	0,30	7,25
DTOF000004	OPERATIONAL	C	MAIN	10/08/2024	08:05	15:20	FM-GA	0,30	7,25
DTOF000004	OPERATIONAL	C	MAIN	10/08/2024	16:10	23:30	FM-GA	0,31	7,33
DTOF000004	OPERATIONAL	C	MAIN	11/08/2024	00:05	07:25	FM-GA	0,31	7,33
DTOF000004	OPERATIONAL	C	MAIN	11/08/2024	08:05	15:26	FM-GA	0,31	7,35
DTOF000004	OPERATIONAL	C	MAIN	12/08/2024	00:05	07:30	FM-GA	0,31	7,42
DTOF000004	OPERATIONAL	C	MAIN	12/08/2024	08:05	15:40	FM-GA	0,32	7,58
DTOF000004	OPERATIONAL	C	MAIN	12/08/2024	16:05	23:30	FM-GA	0,31	7,42
DTOF000004	OPERATIONAL	C	MAIN	13/08/2024	00:05	07:35	FM-GA	0,31	7,50
DTOF000004	OPERATIONAL	C	MAIN	13/08/2024	08:05	15:35	FM-GA	0,31	7,50
DTOF000004	OPERATIONAL	C	MAIN	13/08/2024	16:08	23:30	FM-GA	0,31	7,37
DTOF000004	OPERATIONAL	C	MAIN	14/08/2024	00:05	07:30	FM-GA	0,31	7,42
DTOF000004	OPERATIONAL	C	MAIN	14/08/2024	08:05	15:30	FM-GA	0,31	7,42
DTOF000004	OPERATIONAL	C	MAIN	14/08/2024	16:05	23:30	FM-GA	0,31	7,42
DTOF000004	OPERATIONAL	C	MAIN	15/08/2024	00:05	07:30	FM-GA	0,31	7,42
DTOF000004	OPERATIONAL	C	MAIN	15/08/2024	08:10	15:30	FM-GA	0,31	7,33
DTOF000004	OPERATIONAL	C	MAIN	15/08/2024	16:07	23:20	FM-GA	0,30	7,22
DTOF000004	OPERATIONAL	C	MAIN	16/08/2024	00:05	05:45	FM-GA	0,24	5,67
DTOF000004	OPERATIONAL	C	MAIN	16/08/2024	08:15	15:25	FM-GA	0,30	7,17
DTOF000004	OPERATIONAL	C	MAIN	16/08/2024	16:15	23:30	FM-GA	0,30	7,25
DTOF000004	OPERATIONAL	C	MAIN	17/08/2024	00:05	07:30	FM-GA	0,31	7,42
DTOF000004	OPERATIONAL	C	MAIN	17/08/2024	08:15	15:25	FM-GA	0,30	7,17
DTOF000004	OPERATIONAL	C	MAIN	18/08/2024	00:05	07:35	FM-GA	0,31	7,50
DTOF000004	OPERATIONAL	C	MAIN	18/08/2024	08:10	13:35	FM-GA	0,23	5,42
DTOF000007	OPERATIONAL	C	MAIN	06/08/2024	16:10	23:30	FM-GA	0,31	7,33
DTOF000007	OPERATIONAL	C	MAIN	07/08/2024	08:05	15:20	FM-GA	0,30	7,25
DTOF000007	OPERATIONAL	C	MAIN	07/08/2024	16:15	23:32	FM-GA	0,30	7,28
DTOF000007	OPERATIONAL	C	MAIN	08/08/2024	08:05	15:30	FM-GA	0,31	7,42
DTOF000007	OPERATIONAL	C	MAIN	08/08/2024	16:03	23:25	FM-GA	0,31	7,37
DTOF000007	OPERATIONAL	C	MAIN	09/08/2024	16:10	23:25	FM-GA	0,30	7,25
DTOF000007	OPERATIONAL	C	MAIN	10/08/2024	08:05	15:20	FM-GA	0,30	7,25
DTOF000007	OPERATIONAL	C	MAIN	10/08/2024	16:05	21:55	FM-GA	0,24	5,83
DTOF000007	OPERATIONAL	C	MAIN	11/08/2024	00:13	07:23	FM-GA	0,30	7,17
DTOF000007	OPERATIONAL	C	MAIN	11/08/2024	08:10	15:20	FM-GA	0,30	7,17
DTOF000007	OPERATIONAL	C	MAIN	12/08/2024	00:05	07:30	FM-GA	0,31	7,42
DTOF000007	OPERATIONAL	C	MAIN	12/08/2024	08:10	14:00	FM-GA	0,24	5,83
DTOF000007	OPERATIONAL	C	MAIN	13/08/2024	00:05	05:40	FM-GA	0,23	5,58
DTOF000007	OPERATIONAL	C	MAIN	13/08/2024	08:10	15:35	FM-GA	0,31	7,42
DTOF000007	OPERATIONAL	C	MAIN	14/08/2024	08:05	13:15	FM-GA	0,22	5,17

i wanna make uttilization base on this table on power bi 

if i import file excel to power bi, this data will be  static, so i select data base on server (until current time)

please help to get active time on power bi

file powerbi and excel complete

@OwenAuger 
@bhanu_gautam 
@sjoerdvn 
@Jihwan_Kim 

[Anonymous · Accepted solution]

Hi, @EricoVincentciu 

Based on the data you have provided, please see if this is your desired outcome.

Measure1 = CALCULATE(SUM('Table'[jam]),ALLEXCEPT('Table','Table'[internal_id]))

Measure2 = CALCULATE(COUNT('Table'[internal_id]),ALLEXCEPT('Table','Table'[internal_id]))

Measure3 = DIVIDE([Measure1],[Measure2])

Datediff = 
Var _date1=CALCULATE(MIN('Table'[operational_date]),ALLEXCEPT('Table','Table'[internal_id]))
Var _date2=CALCULATE(MAX('Table'[operational_date]),ALLEXCEPT('Table','Table'[internal_id]))
RETURN
DATEDIFF(_date1,_date2+1,HOUR)

Result = DIVIDE([Measure3],[Datediff])

Please see the attached document.

Best Regards,

Community Support Team _Charlotte

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