Forum Discussion
Can I create a custom Matrix visual that can hide Values on columns?
Hi,
I am working on creating a matrix that shows Actual vs Budget with the actuals on a weekly level and then its totals compared to Budget.
However, by default Power BI adds Values to all columns. What I am missing is an option to toggle visibility of certain Values on either Columns+Totals, Columns only or Totals only.
To illustrate, I want to go from:
To:
As you can see I already found a workaround, but it is not a recommended workflow for professional use in my opinion. What I did:
- Clear values on weeklevel for Budget, Delta Budget and % values by using HASONEVALUE check with DAX
- Disable Word Wrap on Column headers in Matrix settings
- Shrink all unwanted columns to 0px
- (Temporarily set a default value for those columns to be able to hide them for the coming weeks of the year, otherwise when a new week passes, the columns would be shown again)
But this involves manually hiding hundres of columns and I have no idea how reliable this is and that Power BI doesn't suddenly show them again to users when they interact with the report.
One alternative would be to make two tables and place them right after each other, and hide the labels on the second table and keep same order and use 'show empty values' to ensure that they display the same amount of rows, but then I loose my interaction: If I collapse a level in the matrix on the one, it does not collapse on the other.
So I would like to learn if this is a use case for designing a custom matrix viz? I have not worked with custom visuals yet. Is this something I could build in a day or two or would it require serious work and specific (programming?) skills?
Also, if anyone else knows a better workaround please share as well 😉 A little show & tell on my part as the current workaround might work for some, but I am looking for a more robust solution.
3 Replies
- parry2k
Super User
Martin1986 see attached pbix, and use a similar technique to visualize your data
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
- Martin1986Frequent Visitor
Thank you. I think I understand the technique used. But struggling to see how I would use it with dynamic columns (Year-Week) rather than fixed columns?
- AnonymousNot applicable
HI Martin1986,
Power bi does not support dynamic column based on filter/slicers, maybe you can take a look at following blog to know how to create a custom table that can interact with filters dynamically:
Dynamic Attributes In A Power BI Report
Regards,
Xiaoxin Sheng