Forum Discussion
How can I recreate this Excel-style grouped header layout with a Total section at the end
Hi everyone,
I am trying to recreate an Excel-style report layout inside a Power BI Matrix visual or if there is another way. I’ve built most of it, but I’m struggling to reproduce the final Total section at the end exactly like the Excel version.
Below is a dummy example of my header structure:
| Product A | Product B | totals | |||||||
| Country | Region | Sales A | Sales B | Sales A | Sales B | Sales A | Sales B | Sales A + B | Totals % |
| Usa | North | 10 | 2 | 13 | 23 | 2 | 25 | 49% | |
| USA | south | 11 | 3 | 12 | 11 | 15 | 26 | 51% |
✔ IMPORTANT REQUIREMENT
I must keep the grouped product headers exactly like Excel:
A single group header for each product (Product A, Product B, Product C, Product D)
Two sub-columns under each (Metric A and Metric B)
Then a separate “TOTALS” group with three different measures:
Total Amount
% of Total
Grand Total
This grouped structure is essential for my report.
The Problem in Power BI
I can create:
Category and Region as rows
Product A → Product D as column groups
Metric A / Metric B as sub-columns under each product
A totals group using column subtotals
However, when I add the extra measures for the TOTALS section (Total Amount, % of Total, Grand Total):
❌Power BI automatically puts those measures under every product group
(Product A, Product B, Product C, Product D)
and not just under the final Totals section.
Even if I use ISINSCOPE() to blank the values, the headers still appear, creating empty columns and breaking the visual layout.
My Question:
Is there ANY way in Power BI Matrix to:
Keep the grouped product headers and sub-grouping exactly like Excel,
AND show specific measures only under the final “TOTALS” group,
WITHOUT showing empty headers under each product?
Or, if the matrix visual simply cannot do this:
What is the recommended workaround to achieve this Excel-style layout?
Is there a modeling technique or custom visual that supports asymmetric columns like this?
Any help or suggestions would be greatly appreciated — especially from anyone who has built complex Excel-style matrices in Power BI.
Hi,
This may be possible to solve with calculation groups. Share the download link of the PBI file.
5 Replies
- amitchandak
Super User
Anonymous , I assume for few measures you only want Grand total. Add item as an item in the calculation group, use isinscope, and make them blank when the product isinscope
calculation items
Sales A = [Sales A]
Sales B = [Sales B]
Total % = if(isinscope(Product[Product]) , blank(), [Totals %])
How to Show Measure Only In Total Column - https://youtu.be/Eh4wJtGjSEE
for more complex format
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0
- Ashish_Mathur
Hi,
This may be possible to solve with calculation groups. Share the download link of the PBI file.
v-echaithraCommunity Support
Hi Anonymous ,
This is a known and inherent limitation of the native Power BI Matrix visual. No, the native Matrix does not support Excel-style asymmetric column groups, where product groups contain one set of measures and a separate TOTALS group contains a different set of measures, without rendering empty headers.
This behavior is by design and is not related to data modeling or DAX logic.As a workaround, you can simulate the layout using alternative approaches (for example, splitting the layout across multiple visuals or using a disconnected header pattern), depending on your report and governance requirements.
Two Matrix visualsMatrix 1: Products (Sales A / Sales B)
Matrix 2: TOTALS (Total / % / Grand Total)
Align them side-by-side using containers
Turn off gridlines and headers where needed
Hope this helps.
Chaithra E.- v-echaithra
Hi Anonymous ,
I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know, we’re happy to help!
Thank you
Chaithra E. - v-echaithra
Hi Anonymous ,
May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.
Thank you.
