Forum Discussion
Matrix with dynamic years only for specific measures (hide other measures per year)
I have several metrics calculated and want to display them in a table or matrix view, but I want two of the metrics to show the values broken down by year. However, if I put the year in the columns, it displays the year in all the metrics.
Basically, I want to do this: in the “Total” (FT) column and the “Actual Total Costs” column, in addition to the total value, the breakdown by year should appear, but not in the other columns. Is it possible to do this in BI?
Translated with DeepL.com (free version)
The matrix in Power BI applies a single column hierarchy to every value field, so natively you cannot have Year break down only some measures and not others inside one matrix.
The cleanest workaround is to place two matrix visuals side by side, sharing the same row fields. One has Year on Columns with only your two year-broken-down measures, the other has no column field and contains the rest of the measures. Align their widths and hide the duplicate row header on the second one for a seamless look.
If everything has to stay in one visual, you can wrap the non-year measures so they return BLANK when Year is in scope and the actual total when it is not, for example:
Total Cost (no year) = IF(ISINSCOPE('Date'[Year]), BLANK(), [Total Cost])The year column headers will still appear but the cells will be empty for those measures. In practice the two-visual approach looks cleaner.
If that was helpful, please give a thumbs up and mark it as resolved.
Best regards,
Shai Karmani
3 Replies
- Shai_Karmani
Super User
The matrix in Power BI applies a single column hierarchy to every value field, so natively you cannot have Year break down only some measures and not others inside one matrix.
The cleanest workaround is to place two matrix visuals side by side, sharing the same row fields. One has Year on Columns with only your two year-broken-down measures, the other has no column field and contains the rest of the measures. Align their widths and hide the duplicate row header on the second one for a seamless look.
If everything has to stay in one visual, you can wrap the non-year measures so they return BLANK when Year is in scope and the actual total when it is not, for example:
Total Cost (no year) = IF(ISINSCOPE('Date'[Year]), BLANK(), [Total Cost])The year column headers will still appear but the cells will be empty for those measures. In practice the two-visual approach looks cleaner.
If that was helpful, please give a thumbs up and mark it as resolved.
Best regards,
Shai Karmani 
v-menakakotaCommunity Support
Hi fpereira ,
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank Shai_Karmani , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.Best Regards,
Community Support Team- v-menakakota
Hi fpereira ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.
Best Regards,
Community Support Team