Forum Discussion
Have issues with using a matrix table
Is there any good ways to build a grouped table similiar to a crystal report in powerbi?
I am trying to build something similar to this. I built this report in Crystal but trying to move to powerbi so users have less programs to use.
The basic idea is i have 3 groupings.
1. Date
2. Vendor Name
3. Truck ID #
Once, I have the grouping down I have 12 detail fields that would go under the last group truck id#. I was trying to use a matrix table but I am not having any luck. When i added the 3 fields to the rows it was looking good ,but then when i added the detail fields to the values spot that is when everything started to break. It only wants to show the first value when i really need all the values.
Hi alex20p
Thank you for reaching out to the Microsoft Community Forum.
To build a grouped table in Power BI that mimics the Crystal Report style you shared with nested groups: Date → Vendor Name → Truck ID → Detail Fields**, here's a structured way to achieve it. 3 levels of grouping: Date > Vendor Name > Truck ID, Multiple detail fields displayed in a row-like format under each Truck ID, a total row or summary at the bottom.
Use Table Visual + Grouping in DAX, The Table visual is better suited for your case than Matrix when you want to show many fields side-by-side per row without aggregation.
Grouping Lines (Mimic Crystal’s Expand/Collapse):Power BI doesn't support native nested expand/collapse for tables the way Crystal Reports does, but you can mimic it:Use a slicer or bookmark-based toggles to show/hide details or break the layout into multiple visuals per level (Date header, Vendor section, Details)
if you use matrix visual (Only if You Want Aggregated Data), if you're summarizing data (e.g., totals per vendor), you can use a Matrix with:Rows: Date > Vendor Name > Truck ID
Values: Only measures (like sum of weight, volume, etc.)
But this won’t display raw fields side-by-side. It’s best for numeric summaries, not detailed row data.
If Power BI is showing only one value or blank in the Values area:- Ensure your values are not aggregated unintentionally.
- Instead of putting fields in “Values”, put them in Table columns directly.
- If you must use Matrix, convert details into measures with SELECTEDVALUE() if needed:
DAX : Truck_Location = SELECTEDVALUE(Truck[Location])
Final Touch: Grand TotalsIn the Table visual, to show totals like "Total Truck" row, Use a measure that dynamically changes label based on row context:
DAX : Truck_Label = IF(HASONEVALUE(Truck[Truck ID]),MAX(Truck[Truck ID]),"Total Truck")
If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.
Thank you.
6 Replies
Hi alex20p ,
Yeah, Power BI’s matrix visual can be a bit tricky when you're trying to replicate Crystal Reports-style grouping with multiple detail fields.
Here’s how you can approach it:
1. Use the Matrix visual
- Add Date, Vendor Name, and Truck ID to the Rows section (in that order).
- Add your 12 detail fields to the Values section.
2. Important: Use measures instead of raw columns
Power BI matrix expects aggregation in the Values area. If you're adding raw fields, it’ll only show the first value or blank. You’ll need to create measures like:
Detail1 = MAX('YourTable'[Detail1]) Detail2 = MAX('YourTable'[Detail2])Repeat for each detail field. This ensures the matrix can aggregate and display them properly.
3. Turn off “Stepped Layout”
In the matrix visual formatting pane:
- Go to Row headers
- Set Stepped layout to Off
This will give you a flat, grouped layout more like Crystal Reports.
Let me know if you need help building the measures or formatting the layout further.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AIburakkaragoz thanks for your response. I tried your suggestion and my data is just not working correctly with the matrix. the problem is there could be mutiple values tied to a grouping so when i use max i am still only getting 1 value and dropping off the rest. Thanks again for trying.
Hi alex20p
Thank you for reaching out to the Microsoft Community Forum.
To build a grouped table in Power BI that mimics the Crystal Report style you shared with nested groups: Date → Vendor Name → Truck ID → Detail Fields**, here's a structured way to achieve it. 3 levels of grouping: Date > Vendor Name > Truck ID, Multiple detail fields displayed in a row-like format under each Truck ID, a total row or summary at the bottom.
Use Table Visual + Grouping in DAX, The Table visual is better suited for your case than Matrix when you want to show many fields side-by-side per row without aggregation.
Grouping Lines (Mimic Crystal’s Expand/Collapse):Power BI doesn't support native nested expand/collapse for tables the way Crystal Reports does, but you can mimic it:Use a slicer or bookmark-based toggles to show/hide details or break the layout into multiple visuals per level (Date header, Vendor section, Details)
if you use matrix visual (Only if You Want Aggregated Data), if you're summarizing data (e.g., totals per vendor), you can use a Matrix with:Rows: Date > Vendor Name > Truck ID
Values: Only measures (like sum of weight, volume, etc.)
But this won’t display raw fields side-by-side. It’s best for numeric summaries, not detailed row data.
If Power BI is showing only one value or blank in the Values area:- Ensure your values are not aggregated unintentionally.
- Instead of putting fields in “Values”, put them in Table columns directly.
- If you must use Matrix, convert details into measures with SELECTEDVALUE() if needed:
DAX : Truck_Location = SELECTEDVALUE(Truck[Location])
Final Touch: Grand TotalsIn the Table visual, to show totals like "Total Truck" row, Use a measure that dynamically changes label based on row context:
DAX : Truck_Label = IF(HASONEVALUE(Truck[Truck ID]),MAX(Truck[Truck ID]),"Total Truck")
If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.
Thank you.Hi alex20p
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Akhil.
Hi alex20p
Just checking in to see if the solution we shared worked for you? If you're still facing any issues or need more help, feel free to let us know, we're happy to assist further.
If the response helped resolve your issue, please consider marking it as Accepted as Solution and give it a thumbs up if you found it helpful.
Thanks,
Akhil.
Hi alex20p
Just checking in, were you able to try out the steps we discussed earlier? If it worked, that’s great to hear! Feel free to mark the solution that helped it will help other community members.
If you're still running into issues, let us know what you’re seeing even small details can help us troubleshoot more effectively.
Regards,
Akhil
