sum of group of values

[professional65]

Dears,

I have a huge amount of data  but to summarize, I show them as bellow as an example: 

Field	Well Name	User Code	DURATION	start depth	end depth
caril	CRL1	DRL1	1
caril	CRL1	DRL1	0,5	1500	1800
Canela	CAN2	BOP	2
Canela	CAN3	BOP	2	null	null
Canela	CAN4	DRL1	1	1100	1150
caril	CRL2	DRL1	3	1300	1430
caril	CRL3	CMT1	1	null	null
Canela	CAN2	CM1	2	null	null
Canela	CAN3	BOP	4	null	null
Canela	CAN3	DRL1	5	1300	1600
caril	CRL1	DRL1	6	null	null
caril	CRL2	DRL1	5	2200	2400
caril	CRL1	DRL1	7	1820	2150
caril	CRL1	DRL1	2	300	450
Canela	CAN2	BOP	3	null	null
Canela	CAN3	BOP	1	null	null
Canela	CAN2	BOP	2	null	null
Canela	CAN3	BOP	4	null	null
Canela	CAN3	DRL1	1	1200	1230
caril	CRL1	BOP	3	null	null
caril	CRL2	CMT1	1	null	null
caril	CRL1	CMT1	5	null	null
caril	CRL1	BOP	2
caril	CRL2	DRL1		2150	2200
caril	CRL1	DRL1	0	2200	2200
caril	CRL1	BOP	1	null	null
caril	CRL2	CMT1		null	null
caril	CRL1	DRL1	null	750	900

I need to get average of duration for each distinct User Code for total project and then I can make a filter  (slicer) to filter based on Fields. But for that, I should also consider that for each well (in Well Name column), the real value related to each user code is sum of durations for a user code in same well. as follow

Field	Well Name	User Code	DURATION
Canela	CAN2	BOP	7
CM1	2
CAN3	BOP	11
DRL1	6
CAN4	DRL1	1
caril	CRL1	DRL1	16,5
BOP	6
CMT1	5
CRL2	DRL1	4
CMT1	1
CRL3	CMT1	1

where Duration for BOP user Code for CAN-2 IS 2+3+2 = 7
I need to make an Average duration of each user code for each well. 
I make this formula but it does not give me the real result: 

Ave Total =

CALCULATE(

    AVERAGEX(

        SUMMARIZE(V_WVT_WVJOBTIMELOG, V_WVT_WVJOBTIMELOG[User Code], "Ave Total",

       AVERAGE(V_WVT_WVJOBTIMELOG[DurationPerHour])),

        [Ave Total]

    )

)

Also I need to determin a measure to calculate the (Interval =End Depth - Start Depth) / Duration, by adding a condtion that if the duration is 0, consider the result as a null value and if Duration = 0 consider it as blank!

Thanks and Regards

[Anonymous]

Hi @professional65 ,

To calculate an Average duration of each user code for each well, you can try to use this DAX to create a calculated column:

Average_Duration = 
CALCULATE(
    AVERAGE('Table'[DURATION]),
    ALLEXCEPT('Table', 'Table'[Well Name ], 'Table'[User Code])
)

And the final output is as below:

And I quite can't understand what do you mean by "if the duration is 0, consider the result as a null value and if Duration = 0 consider it as blank!", don't the conditions before and after "and" mean the same thing? Or are duration and Duration two different things?
And I can give you an example to calculate the (Interval =End Depth - Start Depth) / Duration:

Interval = 
IF(
    [DURATION] = BLANK(),
    BLANK(),
    ([end depth] - [start depth]) / [DURATION]
)

The output is as below:

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