Forum Discussion
Solved
Values not averaging correctly based on date
I have two tables in my source, each with a Month and Year value (which have a many to many relationship). When the values are averaged based on Month or Week, either the average occupany or tickets ...
Hi,
PBI file attached.
Hope this helps.
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.