Have you ever come across an issue where your Power BI matrix column widths just aren’t the same width and visually just don’t look right?
Unfortunately (as of April 21’) there is no easy way to make all column widths equal in the format pane of a matrix visual.
However there is a hack to set the width of your all columns in a matrix so that they are all equal and pixel perfect with DAX!
How do you do this I hear you ask?
Firstly create a new measure called ‘Set Column Width’ and enter a string value equal to the length of your longest column title. In my case my longest title is “Front Derailleur Cage” and this has a length of 21 characters including spaces. Therefore I need to set my DAX expression to be a string which is 21 characters long. In my example I’ve just created a string of 21 asterixis wrapped in speech marks – but this can be any combinations of characters you like!
The next job is to go to the format pane of your matrix and set the “Show on rows” toggle equal to ‘On’.
Next make sure the ‘Auto-size column width’ is set equal to ‘On’.
Now drag your newly created DAX expression (in my case ‘Set Column Width’) on to the values field of your matrix.
You’ll then notice that your matrix will look similar to the snip below – a little bit of a mess! But not to worry this all part of the plan!
Next go back the format pane of your matrix and set ‘Auto-size column width’ to ‘Off’.
Now remove your ‘Set Column Width’ measure from the visual by clicking the ‘X’ symbol on the field pane.
Finally increase the width and height of your matrix visual to accommodate the increased column widths.
You now have pixel perfect column widths which are all equal to each other!
A huge thanks to the brilliant MVP Ruth Pozuelo Martinez (@ruthpozuelo) from curbal.com for this hack! It’s a been a massive help for my Power BI reports here at Purple Frog! Hopefully the Power BI team will release a proper solution in the matrix format pane soon!
Great hack. As someone who is OCD about symmetry, this was a gem.
hi Nick, thanks for this but it doesnt seem to work. when i drag the “set width” measure into my matrix it is shown beside my real measure and not below it like you have demo here. any solution?
Thanks for your message.
Could you check you have got the “Show on rows” toggled to “On” in the format pane of your matrix?
When I remove the “Pad”measure the widths revert to the preset ones…
Did you set Auto-size column width to ‘Off’ after you added the measure?
Not only did this help me with column width, but I also had no idea about “Show on Rows”. Brilliant – thanks!
No problem – I’m glad it helped!
Great hack , and so simple to follow , don’t think it works for me as i want to wrap the text to make them narrower.
Thanks for the post. It helped to fix the column width which is not yet available in Power Bi. The method is working for fixing the column width of all values columns except for Total columns. I turned off word wrap. Is there any turn around for this or do I need to format differently for totals?
I have data only in Rows and Values; there is nothing in Columns for Matrix. In this scenario, how to fix the column width.
Unfortunately my guide only works if there are values in Columns, Rows and Values.
However, I think it could be possible if you played with adding and removing dummy width data from your underlying model and switching to a table instead of a matrix.
For example, in your underlying data you could add a new row where Month = “**********” and Sales = 1234567891 and then follow a similar method highlighted in the blog post before deleting the row thereafter.
Brilliant hack!!! Thank you so much 🙂
This is not working when the column headers are too long! I like the solution though. Still hope there is another way for the data with long column headers 🙁
If you let me know your maximum column header length I can take a look!
Hi Nick, I have same question as of Chau. Let’s say the maximum column header length is 20 or 25 but I want the column width to be 15 and column header text to be wrapped. How do take care of this scenario?
Hi, cool solution for when the column headers are shorter than what you want. My current column headers are very long so to fit them all on the page I want to limit the width to 8 characters and have the text wrap.
This is possibly the greatest PBI hack I’ve ever seen! Thanks 🙂
Good solution if the visual only uses a single value field but I could not manage to apply it on a visual with two value fields. Managed to make it work by renaming the value fields directly to “**********”, turned the autowidth for columns off and then named them back to the initial value. Thanks!
Doesn’t work when switching show on rows back off again.
I think you may have missed a step – let me know if it still doesn’t work.