cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## AVERAGEX, SUMMARIZE, 0 for no data

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

3 REPLIES 3
Frequent Visitor

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.
Community Support

Hi @Fabi ,

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

Best Regards,

Frequent Visitor

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 !

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.