AVERAGEX, SUMMARIZE, 0 for no data

[Fabi]

Hi all !
I have several conditions to respect in my calculation: for a station, I must count the number of activities (type 1, 2 or 3 only) that took place on three different services (DAY, NIGHT and MIXED) per month. When two or more activities took place on my station during the month for a service, the value must be 1, if there was only one activity then the value must be 0.5, if zero, 0.
I then need to average this calculation.

I created several measures that allow me to almost achieve the expected result:
The first calculates the number of activities according to type:

NumberActivities =
CALCULATE (
DISTINCTCOUNT(Data[ID_Act]),
Data[Type_Act] IN {"1", "2", "3"}
)​

The second assigns the desired value 1, 0.5 or 0 :

ValueActivities =
SWITCH(
TRUE(),
[NumberActivities] = 0, 0,
[NumberActivities] = 1, 0.5,
[NumberActivities] >= 2, 1
)

The third which poses a problem for me calculates the average according to the month/year, station and service conditions:

AvgValues =
AVERAGEX(
SUMMARIZE(
'Data',
'Data'[Station],
Data[Sevice],
Data[Month/Year],
"Valeur",
[ValueActivities]
),
[Valeur]
)

In fact, this calculation does not take into account the 0 in the average when there is no data for the service. 

In the following example, for station S1 in march 2024 the average should be 0.66

In this one, in april 2024, the average is good because it takes into account 0 for the MIXED, because there was indeed an activity even if it is not of type 1, 2 or 3 (4 here) :

How can I have 0.66 for the first exemple ?

Thank you all. 

My datas : 

ID_Act	Sevice	Date	Station	Type_Act	Month/Year
A1	DAY	01/03/2024	S1	1	32024
A2	NIGHT	02/03/2024	S1	1	32024
A3	NIGHT	04/03/2024	S1	2	32024
A4	DAY	06/03/2024	S1	2	32024
A5	DAY	06/04/2024	S1	3	42024
A6	NIGHT	04/04/2024	S1	3	42024
A7	MIXED	02/04/2024	S1	4	42024
A8	MIXED	01/04/2024	S1	4	42024
A9	DAY	01/03/2024	S2	1	32024
A10	DAY	02/03/2024	S2	2	32024
A11	NIGHT	04/03/2024	S2	3	32024
A12	NIGHT	06/03/2024	S2	4	32024
A13	NIGHT	12/03/2024	S2	3	32024
A14	NIGHT	11/03/2024	S2	2	32024
A15	MIXED	10/03/2024	S2	1	32024
A16	MIXED	09/03/2024	S2	2	32024

[Fabi]

I found the solution:
I divided the last measurement into 3 different measurements, one for night, one for day and the last for mixed.
Like this :

AvgValueNight = AVERAGEX(

   SUMMARIZE(

       'data',

       data[Station],

        data[Month/Year],

       "Valeur",

       CALCULATE([ValeurActivities], data[Service] = "NIGHT")

   ),

   [Valeur]

)

So, I have a 0 if there is no value for the service.

I then made a measurement for the overall:

([AvgValueNight]+[AvgValueDay]+[AvgValueMixed])/3

This works because we only have 3 values ​​for the service and we do not put "Services" filters on our report page.
If this were not the case, another solution would have to be found.

[v-kongfanf-msft]

Hi @Fabi ,

[Month/Year] This field does not seem to match the test data you provided.

Best Regards,
Adamk Kong

[Fabi]

Hi @v-kongfanf-msft ,
Indeed, it is a column that I had calculated in my report. I just added it to the table from my previous post. Thanks !