In Report Builder matrix, how to make Row Groups stay in the same level and visible in the results

[OceanExplorer]

Hello, in Report Builder, I created a Matrix visual to calculate total payment groupped by each State and Customer ID. For example, this is the original data: 

I placed State and Customer ID in Row groups,  Payment in Values and changed it to Sum in the below panel:

The calculation returns correct, however, the result will hide State name if they belong to the same group, like below:

My desired result is showing all Row groups names in each row, as below:

I tried to turn off Expand/collapse groups but it didn't help, 

I reality, I will need to Sum payment groupping by field 1, 2, 3, 4... and more, how to display all field names in each row without showing that "hierarchy" format?

Thank you!

 

 

 

 

[v-tsaipranay]

Hi @OceanExplorer ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @OceanExplorer ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @OceanExplorer ,

Thank you for reaching out to the Microsoft Fabric Community. Also thank you @tharunkumarRTK  and @rohit1991  for your inputs.

To display all row group names (like State and Customer ID) on every row in your Matrix removing the hierarchy-style merged cells, please do the following:

1. In your matrix, add the group fields (e.g., =Fields!State.Value) directly to the detail cells, not just the row group headers.
2. This forces Report Builder to show the values on each row, rather than merging them for repeated values.
3. Optionally, disable "CanGrow" on the textbox (via Properties pane) and ensure "Visibility" is set to Show in Textbox Properties.

This will flatten the layout and repeat group names on every row, matching your expected output.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

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.

Thankyou.

[OceanExplorer]

Hi @v-tsaipranay , I didn't quite get your methods. In order to add group fields into details cell, the visual has to be a flat table not a Matrix. If I do this, the last column will not display a summed value groupping by the other fields. Could you be more specific about which fields need to be added to which cell below?
Left is a Table visual, right is a Matrix visual:

 

[v-tsaipranay]

Hello @OceanExplorer ,

Thank you for your clarification! You're right that adding group fields directly to the Table will lose the automatic grouping behavior of the Matrix, particularly for calculated totals.

To maintain the sum functionality while achieving the flat, repeated-row look, we should stick with the Matrix visual. First, in the Row Groups pane, right-click on your last group (e.g., CustomerID) and select Insert Row → Inside Group – Below. Then, manually populate the new row with the group fields: =Fields!State.Value, =Fields!CustomerID.Value, and =Sum(Fields!Payment.Value).

This ensures all group values are displayed on each row. If the group header rows remain visible, either delete or hide them. For additional group fields, repeat this process for each level. This method will give you a flat table layout while preserving accurate totals.

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.

Thankyou.

[OceanExplorer]

Hi @v-tsaipranay , thank you for the instructions. I tried to follow but was still kind of lost. Is it possible for you to share an simple example with some step-by-step screen shots?

[v-tsaipranay · Accepted solution]

Hi @OceanExplorer ,

Apologies for delayed response and sorry for the confusion before. To clarify Power BI Report Builder’s Matrix visual automatically merges repeated values in row groups to present a hierarchical view. There is no built-in toggle to display all row group values on every row like a flat table. However, we can work around this using layout adjustments in the Matrix.

Here’s what you can do:

• Stay with the Matrix visual (to retain grouping and aggregation).
• In the Row Groups pane, right-click on your innermost group (e.g., Customer ID) → select Insert Row > Inside Group – Below.
• In the new row, manually add the group fields:

=Fields!State.Value
=Fields!CustomerID.Value
=Sum(Fields!Payment.Value)

If your dataset is sourced from SQL, you can also pre-aggregate the values using a query like below though this alone won’t affect Matrix layout behavior:

SELECT
  State,
  [Customer ID],
  SUM(Payment) AS TotalPayment
FROM
  YourTable
GROUP BY
  State,
  [Customer ID]

Lastly, if you'd like to see more flexibility in how Matrix visuals handle grouping and repeated values, I recommend submitting this as an idea on the Microsoft Fabric Ideas portal.

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.

Thankyou.

[v-tsaipranay]

Hi @OceanExplorer ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[rohit1991]

Hi @OceanExplorer ,

Yes, it’s absolutely possible to implement this in Power BI using DAX. What you’re looking to do is calculate a rolling average of a KPI (like deposit amount) for the same weekday and hour over the previous 7 occurrences, excluding the current selected date and filtered by country or region. To achieve this, you’ll first need to have a proper DateTime dimension table with columns like date, hour, and weekday name, and ensure it's related to your main data table.

Then, using DAX, you can create a measure that dynamically identifies the last 7 instances matching the same weekday and hour prior to the selected date. This is done using a combination of FILTER, TOPN, and CALCULATETABLE, ensuring the current anomalous date is excluded. Finally, the measure calculates the average KPI over those filtered historical dates. This approach ensures that when a user selects a specific date, hour, and country—like 20th March 13:00 Thursday UK—the measure returns the average of the previous 7 matching timestamps (e.g., 13th March 13:00, 6th March 13:00, etc.), effectively smoothing out anomalies.

[tharunkumarRTK]

@OceanExplorer 

Refer this blog 

https://learn.microsoft.com/en-us/sql/reporting-services/report-design/controlling-row-and-column-he...

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!