Average duration based on a criteria

[Anonymous]

Hello !

I'm trying to calculate the average duration of all my tasks filtred by :

- Column "Nom du compartiment" = "Bench"

- Column "Progression" = "Terminé"

(see picture 1)

The duration of each task is in a column called "soustraction" (created with Power Query)  

 It is "Date de fin"-"Date de début"

(see picture 2)

I tried a lot of things, and there is a mistake on this part : 

CALCULATE (

SUM ( 'Tâches'[Soustraction] )

But I can't find the solution. Here is my complete formula

average =

IF ( COUNTROWS(

FILTER(

ALL('Tâches'),

'Tâches'[Nom du Compartiment]="Bench"&&

'Tâches'[Progression]="Terminé"

CALCULATE (

SUM ( 'Tâches'[Soustraction] )

)

/ CALCULATE (

COUNT ( 'Tâches'[Nom de tâche] ),

COUNTROWS(

FILTER(

ALL('Tâches'),

'Tâches'[Nom du Compartiment]="Bench"&&

'Tâches'[Progression]="En cours"

)

Thanks for advance !

[v-xuding-msft]

Hi @Anonymous ,

I'm not clear about your formula. Is "average" a entire one? If it is, for the formula [CALCULATE (SUM ( 'Tâches'[Soustraction] )], it will return an aggregation value. You can't add it directly into FILTER function. There should be an Boolean expression like ['Tâches'[Nom du Compartiment]="Bench"&&'Tâches'[Progression]="Terminé"] or [&&CALCULATE (SUM ( 'Tâches'[Soustraction] ) >0].

https://docs.microsoft.com/en-us/dax/filter-function-dax

Can you please share the logic of calculation and your expected output? Then we will understand more clearly about your requirement.

[Anonymous]

Hello @v-xuding-msft !

I want to calculate the average number on the "soustraction" column for the lines in which : 

- Progression = "Terminées"

- Nom du compartiment = "Bench"

With my example, I wanted to get (16 + 9 + 9) /3 = 11.33

Thanks for advance !