Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Average duration based on a criteria

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 !

 

 

 

Need Help

2 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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 !