Customize the Matrix visual in Power BI

[Jaykumar31]

Hello,

I have data in the tabular format shown below in fig(A). There are n number of customers using different products. Customers are raising investigation work items, which are represented as numbers in the tabular format fig(A).

Now, we need to display the data in another format in a Power BI report, as shown in fig(B). To achieve this, we have used a Matrix visual to present the data. However, there are a few issues that need to be addressed:

1. Since there are many customer columns, we want to limit the number of columns to 10. The remaining customers' investigation counts should be grouped under 'Others,' which should be placed just before the total column in the expected format fig(B).

2. Some customer names are very long, so we need to fix the column width for all customer columns.

3. Additionally, we need to display all columns in different colors.

Could you please check and let me know the appropriate settings or DAX function?

Fig(A)

Expected Matrix visual in Power BI: fig(B)

[v-saisrao-msft]

Hi @Jaykumar31,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Check the solution provided by @danextian,@fahadqadir3. It outlines the approach for achieving dynamic grouping and highlights key limitations around calculated columns. 

Please refer to the document link below for a better understanding: 

Using calculated columns in Power BI Desktop - Power BI | Microsoft Learn 

To better assist you, could you please share a small sample of your actual data in tabular format? That will help us provide information.

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. 

[danextian]

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/1447...

[Jaykumar31]

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.

[v-saisrao-msft]

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. 

[Jaykumar31]

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.

[v-saisrao-msft · Accepted solution]

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. 

[fahadqadir3]

Hello @Jaykumar31  Review the attached .pbix file and screenshot you need to transform your data. review the steps in power query, similary you need to create additional custom column to color each customer.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Jaykumar31]

Hi @fahadqadir3  - Thanks for the reply. You have split the customer column based on the customer numbers, but the actual customer names contain text values like 'PQR,' 'XYZ,' 'ABC,' etc. Therefore, I can't split using that logic. Additionally, the customer names are very lengthy, so how can I adjust the column width?