Forum Discussion
Helper IIICreate a Power BI Report with stacked colored columns and stacked colored coordinated row values
I clarified and updated my original post. In my below post, I included a picture of the Excel Report with the stacked columns and stacked row values. I also included an example of the workable input data. Please let me know if its best to use Report Builder to create this kind of report?
I would like to create this Excel Report or a similar report in Power BI. The data is coming in as rows, meaning the ColumNames are rows. The values come from a count of the ColumnNames (Columns) by Locality and Referral ID. Please provide guidance or an example of how to accomplish this in Power BI. What is the best visual to use? I am thinking it may have to be a matrix. But, if another visual is better to present this data in a similar and meaningful manner please advise. Or, is it better to use Report Builder to create this excel report? If so, please provide some guidance. Thanks.
Hi PBIFanHook7,
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to danextian and Kedar_Pande for prompt and helpful responses.
Power BI currently has a limitation where conditional formatting cannot be applied to individual row headers or column headers in a Matrix visual formatting can only be applied to the values and subtotals, not the headers themselves. Because of this, recreating the Excel layout with stacked colored column headers and coordinated colored row headers exactly as shown is not fully possible in Power BI Desktop. In real world scenarios where a pixel perfect layout and precise formatting like Excel is required, the better approach is to use Power BI Report Builder (Paginated Reports). Report Builder allows full control over the table layout, lets you group rows and columns, and supports conditional formatting on headers, cells, and totals, making it much easier to reproduce this kind of structured report exactly as designed.
Thanks & Regards,
Prasanna Kumar
6 Replies
- danextian
Super User
Hi PBIFanHook7
Conditional formatting on column and row headers isn't currently supported. Colors are applied to the whole header. It is possible to conditonally format the values and subtotals though (except subtotal header). Also please as always post a workable sample data (not an image) that matches your expected output.
PBIFanHook7Dane, thanks for you response and suggestions. I have updated my post with some clarifications, a new excel layout example with its workable sample data. Hopefully, this helps in getting more feedback and a possible solution. If this can't be done in Power BI, I am wondering would Report Builder be a better option?
- PBIFanHook7
I would like to clarify and improve my original post by providing a new excel layout example and the workable sample data. I also probably should use the term 'stacked columns' instead of wrap-around columns, and 'stacked rows' instead of wrap-around rows. Lastly, I would also ask if this cannot be acheived in Power BI, do you think Report Builder would be a better solution? If so, please provide some guidance. Thanks in advance for all responses.
Excel Layout Example:
Workable Sample Data:
Locality Region Referral_ID ColumnNames TownA Central 1111111 Column1Name_Blue TownA Central 1111222 Column2Name_Blue TownA Central 1111333 Column3Name_Blue TownA Central 1111444 Column3Name_Blue TownA Central 1111555 Column2Name_Green TownA Central 1111777 Column2Name_Green TownA Central 1111888 Column3Name_Green TownA Central 2222111 Column1Name_Yellow TownA Central 2222222 Column1Name_Yellow TownA Central 2222333 Column2Name_Yellow TownA Central 2222444 Column3Name_Yellow TownA Central 2222555 Column1Name_Orange TownA Central 3333111 Column3Name_Orange TownA Central 3333222 Column3Name_Orange TownA Central 3333333 Column1Name_Pink TownA Central 3333444 Column2Name_Pink TownB Central 4444111 Column1Name_Blue TownB Central 4444222 Column1Name_Blue TownB Central 4444333 Column3Name_Blue TownB Central 4444444 Column1Name_Green TownB Central 4444555 Column1Name_Green TownB Central 4444777 Column1Name_Green TownB Central 4444888 Column2Name_Green TownB Central 4444999 Column3Name_Green TownB Central 5555888 Column1Name_Yellow TownB Central 5555999 Column2Name_Yellow TownB Central 7777000 Column1Name_Orange TownB Central 7777111 Column1Name_Orange TownB Central 7777222 Column2Name_Orange TownB Central 7777333 Column3Name_Orange TownB Central 7777444 Column3Name_Orange TownB Central 7777555 Column1Name_Pink TownB Central 7777777 Column1Name_Pink TownB Central 7777888 Column1Name_Pink TownB Central 7777999 Column2Name_Pink TownC Eastern 8888000 Column1Name_Blue TownC Eastern 8888111 Column2Name_Blue TownC Eastern 8888222 Column2Name_Blue TownC Eastern 8888333 Column1Name_Green TownC Eastern 8888444 Column1Name_Green TownC Eastern 8888555 Column2Name_Green TownC Eastern 8888777 Column3Name_Green TownC Eastern 8888888 Column1Name_Yellow TownC Eastern 8888999 Column1Name_Yellow TownC Eastern 9999000 Column1Name_Yellow TownC Eastern 9999111 Column2Name_Yellow TownC Eastern 9999222 Column3Name_Yellow TownC Eastern 9999333 Column2Name_Orange TownC Eastern 9999444 Column3Name_Orange TownC Eastern 9999555 Column1Name_Pink TownC Eastern 9999777 Column1Name_Pink TownC Eastern 9999888 Column2Name_Pink TownC Eastern 9999999 Column2Name_Pink - Kedar_Pande
Use a Matrix visual - perfect for this layout.
Setup:
Rows = Locality, Referral ID
Values = Count of ColumnNames (or unpivot first so ColumnNames become a single field)
Turn on "Show on rows" for values
Format colors per ColumnName using conditional formatting
Hi PBIFanHook7,
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to danextian and Kedar_Pande for prompt and helpful responses.
Power BI currently has a limitation where conditional formatting cannot be applied to individual row headers or column headers in a Matrix visual formatting can only be applied to the values and subtotals, not the headers themselves. Because of this, recreating the Excel layout with stacked colored column headers and coordinated colored row headers exactly as shown is not fully possible in Power BI Desktop. In real world scenarios where a pixel perfect layout and precise formatting like Excel is required, the better approach is to use Power BI Report Builder (Paginated Reports). Report Builder allows full control over the table layout, lets you group rows and columns, and supports conditional formatting on headers, cells, and totals, making it much easier to reproduce this kind of structured report exactly as designed.
Thanks & Regards,
Prasanna Kumar
- PBIFanHook7
Thanks for the thorough response. I also would like to thank danextian and Kedar_Pande for there responses. I am going to have to research a different approach.
