Summarize Status Table

[dading]

Hi...im beginner to PowerBI

i have a table as shown below :

eqmtid	timestart	timeend	Status	Duration_hours
S1	06-Apr-2020 07:30:00	06-Apr-2020 08:12:27	A	0.7075
S1	06-Apr-2020 08:12:27	06-Apr-2020 08:12:41	A	0.0039
S1	06-Apr-2020 08:12:41	06-Apr-2020 08:13:53	B	0.0200
S1	06-Apr-2020 08:13:53	06-Apr-2020 08:14:08	C	0.0042
S1	06-Apr-2020 08:14:08	06-Apr-2020 08:14:18	C	0.0028
S1	06-Apr-2020 08:14:18	06-Apr-2020 08:15:48	A	0.0250
S1	06-Apr-2020 08:15:48	06-Apr-2020 08:31:11	D	0.2564
S1	06-Apr-2020 08:31:11	06-Apr-2020 08:41:28	D	0.1714
S1	06-Apr-2020 08:41:28	06-Apr-2020 08:44:11	C	0.0453

and i want to summarize it to be like this

Can you help me please?

eqmtid	timestart	timeend	Status	Duration_hours
S1	06-Apr-2020 07:30:00	06-Apr-2020 08:12:41	A	0.7114
S1	06-Apr-2020 08:12:41	06-Apr-2020 08:13:53	B	0.0200
S1	06-Apr-2020 08:13:53	06-Apr-2020 08:14:18	C	0.0069
S1	06-Apr-2020 08:14:18	06-Apr-2020 08:15:48	A	0.0250
S1	06-Apr-2020 08:15:48	06-Apr-2020 08:41:28	D	0.4278
S1	06-Apr-2020 08:41:28	06-Apr-2020 08:44:11	C	0.0453

thank you.

[parry2k]

@dading add a sum measure and drop all the column in table visual except duration_hours instead use the following measure

Duration Hours Sum = SUM ( YourTable[Duration_hours] )

Would appreciate Kudos 🙂 if my solution helped.

[dading]

@parry2k , what i want is summarize the timestart and timeend base on status

for example status A (1-2 row)

the result will be

timestart = 07:30:00

timeend = 08:12:41

[amitchandak]

@dading ,

Take First/min for timestart and max/last for timeend.

Or You can take summarize

summarize(Table,table[eqmtid],table[Status],"Time Start",Min(table[timestart]),"Time End",Max(table[timeend]),"Duration hours",Sum(table[Duration_hours]))

[dading]

@amitchandak i have tried it, but i got some result missed

for example status A

timestart 07:30:00

timeend 08:15:48 (should be 08:12:41)

[amitchandak · Accepted solution]

@dading , Got it. I created a new start date to be used. an created a visual based on

Solution attached after signature

[dading]

Wow..amazing

this is what i want

thanl you @amitchandak 

[dading]

by the way

@amitchandak Can we do the same thing in power query? how to do that

thanks in advance