Forum Discussion
Customize the Matrix visual in Power BI
Hi Jaykumar31,
Thank you for follow up.I have reproduced your scenario based on the details provided, and I'm able to get the expected output as per your requirements. Specifically:
- The dynamic column headers (Customer Groups) are now truncated to a fixed character length when they exceed the limit.
- The matrix visual layout remains consistent even if new, longer values appear in the data.
ShortCustomerGroup = IF( LEN([CustomerGroup]) > 15, LEFT([CustomerGroup], 12) & "...", [CustomerGroup] )If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Hi Jaykumar31
Key points:
- The approach depends on how dynamic you want the behavior to be. For example, you could create a calculated column to group customers based on the number of items they have—labeling any group above a certain threshold (say, more than 10 items) as "Others." However, note that calculated columns are static and won’t respond to slicer selections. So, if you apply a date filter and want the “Others” group to update dynamically based on the selected range, a calculated column won’t suffice.
- How do you want the width to be fixed? Truncate customer names, add extra preceding and trailing spaces, etc? Clarifying this will help determine the right approach.
- Conditional formatting can help, but it will only apply to non-blank cells. For example, if "Product A + Customer 1" has a blank cell, conditional formatting won't affect it. Also, you’ll need to decide the color logic—whether it should be based on the customer, the total row, or another rule. Note: conditional formatting cannot be applied to row or column headers.
This isn’t a matter of just applying a single setting or function. It may involve a mix of calculated columns, measures, and formatting, and could take a considerable amount of development time.
I can see that you're a new member. Please refer to this post on how to get better answers - https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Thanks danextian - Thanks for the explanation. The provided link will help me quickly find a resolution. Basically, customer names are in text format and quite lengthy, so we want to avoid scrolling up or down. If I can display 4 to 5 customer columns along with the investigation count and group the remaining ones under an 'Others' column just before the Total column, this should solve the problem.
Hi Jaykumar31,
I have reproduced your scenario based on the sample data and achieved the expected output according to your requirement displaying the Top N customers across columns in a matrix, grouping the rest as "Others", and applying distinct color formatting per customer.For your reference, I’m attaching the.pbix file.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Thanks for your reply. Could you please update your file to show how I can fix the column size? In my report, the columns change dynamically and sometimes contain more than 30-50 words. I want to set a fixed size for each column, and if the text exceeds, let's say, 10–15 characters, it should be truncated with '...' at the end.
I used the REPT function to fix the character length in a column, but it did not work out.
Hi Jaykumar31,
Thank you for follow up.I have reproduced your scenario based on the details provided, and I'm able to get the expected output as per your requirements. Specifically:
- The dynamic column headers (Customer Groups) are now truncated to a fixed character length when they exceed the limit.
- The matrix visual layout remains consistent even if new, longer values appear in the data.
ShortCustomerGroup = IF( LEN([CustomerGroup]) > 15, LEFT([CustomerGroup], 12) & "...", [CustomerGroup] )If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.