Set background color for different hierarhies in Matrix

[FotFly]

Hi,

I have a matrix visual that has two fields in the rows and it creates a stepped layout. On that stepped layoutI want for the first hierarchy to have a different color per row that on the second one.
As you can see in the screenshot I want for the entire row of the first hierarchy to have a dark color and for the second to have that light color.

Thank you in advance.

[v-linhuizh-msft · Accepted solution]

Thanks for the reply from pborah.

Hi @FotFly ,

Since I didn't see your reply, I tried to create a test data and implement the requirements in your original question.

This is the test data:

Please follow the steps:

1. In the visualization panel, turn on the Row subtotals function.

2. Expand the value tab to set the background color.

3.Turn off Row subtotals, and the result is as follows:

By the way if you create an approximation of a hierarchical effect in the table as you say, you could try creating an index column and then sort the newly created columns according to the index column so that the order is correct. Here's a link to sort by column that will help you out:

Sort one column by another column in Power BI - Power BI | Microsoft Learn

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[FotFly]

Hi!

Thank you very much.
That was exactly what I was looking for. Now it seems to work.

[pborah]

Hey I know you deleted your original response to my reply. But I got a copy via email. If you care, below is my response -

• If you want a table that dynamically adjusts it's size based on number of rows, as well as the ability to select which columns are included, all for the purpose of being pdf export friendly, a parameterized paginated report might be a better option. Paginated reports also have a matrix visual. Within there, you want to look at row and column grouping tricks which will autmoatically make those headers for you as well you can customize the background color per row header.
• If you throw values from two unrelated tables into one matrix as well as use an unrelated table for slicer values to filter down your composite matrix table, I'd exercise caution as the results may not be correct even though visually it might look just fine.

[FotFly]

Hi, thank you for your answer. I spend 20 minutes on writing my response and I am not sure why I got an error about html content not being valid and then the reply disappeared and I got lazy to rewrite it. 

Either way, your suggestion about paginated reports is a good idea however that will add more cost as we will need more premium per user licences to access the paginated report.

For the second part, I would not be that worried. This is something I use very often and it actually works. I even came up with an idea of applying this on a table visual. I created a new product side table that takes all the product names as a column, then I create a new column using product names and whitespaces to resemble the stepped layout and on that new table I add row for the status. So now the new product column has all the previous products plus the status. I connect the product column to my main table product column and using the fields parameter I am able to have the dynamic columns as well as the stepped layout. And on the table I can apply conditional formatting for the columns so I actually highlight each row.

However, this way the users cannot sort by column since it will break the layout I created. I am not sure how to disable sorting for the table.

[pborah]

It seems you have country and region in Values card as opposed to rows and/or columns. Is that intentional? Is there a better way to present this information or is this layout a hard requirement given to you by your boss/professor? What is the goal of this visual? What business intelligence is being sought from it?

[FotFly]

This is a dummy dataset because I cannot share the original one due to confidentiality restrictions. So, I tried to replicate the essence of what I need. The purpose of this is to create a page that has the status breakdown for all products and that when it is exported in a pdf, all tables are present without any whitespace between them. That would be feasible if there was a way to dynamically adjust the page height as well as the table height based on rows present in the table. Also, the team needed a functionality that allows user to select the columns presented in the tables. Given that, I tried to create a table that has the status breakdown and kind looks like I have sub tables.

In order to do that, I used the matrix visual with stepped layout. On the rows I have added product and status while on the columns section I have added a column from an unrelated table that shows the column names. That unrelated table is being used as a slicer for the user to pick columns (I know that I could have used fields parameter how ever it does not work on matrix). The value is a measure with a switch that returns the corresponding column value based on the string selected on each column of the matrix. Now the last step to create these subtables is to find a way to highlight the status (first item in hierarchy) so that it resembles to a table header.

Not sure whether that answers your question. But this is a good experiment.