Calculated Table creating blank rows in matrix

[wiranqa]

I have a table with monthly dollar sales data with the months in a column and values in another column, broken out by parent, customer, and product. I am trying to create a calculated table to add in 2024 and 2025 year totals and the difference, since in a matrix all the columns get totaled together, not broken out by year. The time is filterable so that the user can select the months they want to look at, so the columns in the calculated table need to be able to change. I have created the calculated table and measures with the code in the screenshots, but I am missing data in my matrix. what am I doing wrong?Calculated tableSales Only measure2024 total example. 2025 is sameAllMeasures measure

[v-pnaroju-msft]

Hi wiranqa,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi wiranqa,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi wiranqa,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi @wiranqa,

Thank you for your inquiry through the Microsoft Fabric Community Forum.

Based on my understanding, the blank rows are occurring because the calculated table, YOY NPS, includes both actual data (month-year combinations) and virtual rows (such as 2024 NPS, 2025 NPS, etc.). These virtual rows do not exist in your fact table, which is why measures like [Sales Only] return blank results when there is no corresponding filter context.

Please follow the steps below to help resolve the issue:

1. Maintain the YOY NPS table as disconnected (without any relationships to your sales data table). This is by design and ensures that it functions effectively as a dynamic column driver in your matrix.
2. Create your base measure (for example, Total NPS) directly in the Sold To Unpivot table, as this is your primary fact/data table and the appropriate location for aggregations.
3. All helper and dynamic measures (such as totals for 2024/2025, differences, and percentage changes) should be developed within the YOY NPS table, as they depend on its column structure and logic.
4. In your matrix visual, utilize YOY NPS[Measure] as the column, and use Parent, Customer, and Product as rows while employing your dynamic measure (e.g., AllMeasures) as the value.
5. No physical relationship is required between YOY NPS and Sold To Unpivot. DAX measures utilizing logical filtering will manage the necessary context.
6. Ensure that the column order in the matrix is sorted by a numeric index within the YOY NPS table to align with the desired output.

If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who may have similar queries.

Thank you.