Sum and average with multiple filters

[Syndicate_Admin]

Hello everyone.

I have a table with data of procedures carried out, such as the following:

Codigo_Obra	Through	Estado_Aprob	Total Term	Meets deadline
2609	15401	APPROVED	42	Defeated
2273	15402	IN PROCESS	20	OK
2272	15403	APPROVED	79	Defeated
2008	15404	APPROVED	79	Defeated
2327	15405	APPROVED	127	Defeated
2582	15406	IN PROCESS	146	Defeated
2445	15407	APPROVED	28	OK
2327	15408	APPROVED	38	Defeated
2327	15409	IN PROCESS	40	Defeated

I need to post the following procedures:

• approved and expired
• approved and ok
• in process and expired
• in process and ok

For which I use for example the following formula:

Tramites_AprobOK = COUNTROWS(FILTER(Data,and(Data[Estado_Aprob]="APPROVED",Data[Meets deadline]="ok")))

Tramites_EnTramiteOK = COUNTROWS(FILTER(Data,and(Data[Estado_Aprob]="In Process",Data[Meets deadline]="OK")))

The above formula is ok??

The same I need to calculate the average number of days it takes to approve a procedure with 2 condciones

• approved and expired
• approved and ok
• in process and expired
• in process and ok

Since I could not filter with 2 conditions and calculate the pomedium, use the following formulas:

Dias_Prom_Aprob = CALCULATE(AVERAGE(Data[Total Term]),Data[Estado_Aprob]="APPROVED")

Dias_AprobOK = AVERAGEX(FILTER(Data,Data[Meets Deadline]="ok") ,Data[Dias_Prom_Aprob])

How do I calculate the average with a single formula?? Or is it ok like that??

Thanks a lot

[v-jingzhang · Accepted solution]

Hi @Syndicate_Admin 

Try something like below for average with multiple conditions.

Dias_AprobOK =
CALCULATE (
    AVERAGE ( Data[Total Term] ),
    Data[Estado_Aprob] = "APPROVED",
    Data[Meets deadline] = "OK"
)

Or

Dias_AprobOK =
AVERAGEX (
    FILTER ( Data, Data[Estado_Aprob] = "APPROVED" && Data[Meets Deadline] = "ok" ),
    Data[Total Term]
)

If you want a blank value to appear like a zero, you can add +0 at the end of your formula. Something like

Dias_AprobOK =
AVERAGEX (
    FILTER ( Data, Data[Estado_Aprob] = "APPROVED" && Data[Meets Deadline] = "ok" ),
    Data[Total Term]
) + 0 

You can use && to connect multiple filtering conditions. See DAX syntax.

Let me know if you have any questions.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Syndicate_Admin]

Sorry for the delay in responding, I was solving other issues.

Thank you very much for the solution, I was able to try the 3 formulas and they work perfectly.

Thanks again

Shortly I will write new doubts

[Syndicate_Admin]

I add to the above that also when I have no data the word white appears and I would like it to look like a zero