Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 !
Jdubois_0-1594394539679.pngJdubois_1-1594394573815.png

 

 

 

2 REPLIES 2
v-xuding-msft
Community Support
Community Support

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.

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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 !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.