calculate total hour

[dading]

Hello all,

i have table like this

Date	Eqmt	Status	duration_hour
01-Jan-20	E1	b	1
01-Jan-20	E1	c	5
01-Jan-20	E2	a	4
01-Jan-20	E2	b	4
02-Jan-20	E1	a	1
02-Jan-20	E1	b	5
02-Jan-20	E1	c	2
02-Jan-20	E2	a	4
02-Jan-20	E2	b	3

i want to create measure to  calculate daily total hour for each eqmt then i calculate status per hour.

example:

total hour of E1 on 01-Jan-20 = 1+5 =6

then i want to get status per hour of b = 1/6 = 0.17

and c = 5/6 = 0.83

the result i want as shown below

Date	Eqmt	Status	duration_hour	Total Hour per Eqmt	Status per hour
01-Jan-20	E1	b	1	6	0.17
01-Jan-20	E1	c	5	6	0.83
01-Jan-20	E2	a	4	8	0.50
01-Jan-20	E2	b	4	8	0.50
02-Jan-20	E1	a	1	8	0.13
02-Jan-20	E1	b	5	8	0.63
02-Jan-20	E1	c	2	8	0.25
02-Jan-20	E2	a	4	7	0.57
02-Jan-20	E2	b	3	7	0.43

Can you help me, please??

[amitchandak]

@dading ,

Try like

new Measure =

sum(Table[duration_hour])/calculate(sum(Table[duration_hour]),allexcept(Table[Eqmt]))

New column =[duration_hour]/sumx(filter(Table,[Eqmt]=earlier(Eqmt)),[duration_hour])

[az38 · Accepted solution]

Hi @dading 

try measure

Total Hour per Eqmt = 
CALCULATE(SUM(Table[duration_hour]), ALLEXCEPT(Table, Table[Eqmt], Table[Date]) )

then measure

Status per hour = 
DIVIDE(SELECTEDVALUE(Table[duration_hour]), [Total Hour per Eqmt])

[dading]

Amazing....

thank you @az38