Values not averaging correctly based on date

Your Month and Year relationships are separate many-to-many joins, not a single combined key. So when no year is selected, the "January" row only filters on Month = January, it pulls in January from both 2025 and 2026, which drags the average from 51.9% down... up to 53.3%. Sum works fine because it's additive; average isn't.

Solution: create a single YearMonth key (Year*100+MonthNumber) in a proper Date table, relate both fact tables to it via one-to-many, and use that Date table's Year/Month in your slicer and matrix instead of the separate many-to-many fields.

Hi,

Based on your description, the issue is most likely related to the data model rather than the AVERAGE() calculation itself.

A few things to check:

• Avoid using a many-to-many relationship between your two fact tables. Instead, create a dedicated Calendar (Date) table and relate both tables to it using one-to-many relationships.

• Verify that your average measure is being evaluated in the correct filter context. When all years are selected, Power BI may be averaging across multiple January values instead of calculating the monthly average you expect.

• If you're using AVERAGE(), consider whether AVERAGEX() over the appropriate date granularity (e.g., days or months) would better match your business logic.

• Use a Matrix visual with Year, Month, and your measures to see how the filter context changes when all years are selected versus a single year.

Since the value is correct when only 2025 is selected, it suggests the calculation is being affected by the multiple years in the current filter context rather than the underlying data.

Could you also share:

• The DAX for your Average Occupancy and Tickets measures?

• Whether you have a separate Calendar table or are using Month/Year directly from the source tables?

• How the relationships between the tables are configured?

The Microsoft documentation on star schema design may also be helpful:
https://learn.microsoft.com/power-bi/guidance/star-schema

If you can share the PBIX or the DAX measures, the community can help identify the exact cause.

If you found this reply helpful, please consider giving it a Kudos. If it helps resolve your issue, marking it as the Accepted Solution will help other community members facing the same problem.

Hi jmorela1,

Thank you for reaching out to Microsoft Fabric Community.

Thank you oussamahaimoud, Ashish_Mathur and Prince0011 for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked?  or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa