Measure: Day Average with specific requirements

[Valentin09]

Hi everyone,

I need help with the following database:

I have got the following formula which works fine: 

DayAverage_Duration =

CALCULATE(AVERAGE(stats_df[total_distance]),

          FILTER(stats_df,(stats_df[activity_participation]= "Full")))

In the activity_tag tab there are the following possiblities:

- Training

- Match

- Training_AM

- Training_Aktivierung

- Lizenz

Now I want the above formula but without data which is tagged as Training_Aktivierung and Lizenz. 

Could somebody help me?

Thanks a lot!

[V-yubandi-msft]

Hi @Valentin09 ,

Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.

[V-yubandi-msft · Accepted solution]

Hi @Valentin09 ,

Thank you for reaching out to the Microsoft Fabric Community. I tested the solution using a sample dataset that matches your scenario. The DAX formula you were provided works correctly.

DayAverage_Duration = 
CALCULATE(
    AVERAGE(stats_df[total_distance]),
    FILTER(
        stats_df,
        stats_df[activity_participation] = "Full"
            && NOT stats_df[activity_tag] IN { "Training_Aktivierung", "Lizenz" }
    )
)

Using the sample data

John Doe → 3100

Alex Miller → 3000

The result is (3100 + 3000) /2= 3050, which I verified in Power BI.

FYI:

Thank you for your response @govind_021  & @mdaatifraza5556 .

 If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[govind_021]

Hi @Valentin09 
Please try this formula , 

CALCULATE(
AVERAGE(stats_df[total_distance]),
FILTER(
stats_df,
stats_df[activity_participation] = "Full" &&
NOT stats_df[activity_tag] IN {"Training_Aktivierung", "Lizenz"}
)
)

Best Regards
Govind Sapkade ( Microsoft Certified Data Analyst , PL 300 Certified , MS Fabric Enthusiast)
Let's Connect
Linkdin - www.linkedin.com/in/govind-sapkade-845104225
Youtube - http://www.youtube.com/@govind_dataanalyst

[mdaatifraza5556]

Hi @Valentin09 

Can you please try the below DAX.

DayAverage_Duration =
CALCULATE(
AVERAGE(stats_df[total_distance]),
FILTER(
stats_df,
stats_df[activity_participation] = "Full"
&& NOT stats_df[activity_tag] IN { "Training_Aktivierung", "Lizenz" }
)
)



If this answers your questions, kindly accept it as a solution and give kudos.