Forum Discussion
Table & Matrix Combo
Hi,
Drag the first 5 columns to the row well of the matrix visual. Drag Year and Month name from the Calendar Table to the Column well of the matrix visual. To sort, specific columns/fields, click on the first column you want to sort and then with e Shift key pressed, click on the others columns which you want to sort by.
Hi meiwah
If I understood you correctly the problem, is that there is no option to sort rows inside matrix columns.
For example, you can't sort the rows by sales of January, I don't think we have the needed functionality/workarounds to solve it.
Only to create a measure for every month.We have an idea about this issue please vote for it:
https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=10822e38-2f28-ec11-b76a-281878bd589b
I voted 🙂
Also, you can check the add-ins like Inforiver / zebra.https://zebrabi.com/power-bi-custom-visuals/
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
To create a table and matrix combo in Power BI that allows for sorting by multiple columns while displaying monthly sales data, you can follow these steps:
Create a Table Visual: Start by creating a table visual for the non-time series data. Include the columns for Country/Region, City, Category, Sub-Category, and Product Name.
Create a Matrix Visual: Next, create a matrix visual for the time series data (monthly sales). Place the ‘Date’ or ‘Month’ field in the columns area and the sales measure in the values area.
Enable Drill Down: On the matrix visual, enable the drill-down functionality. This allows you to view the sales data at different levels of granularity.
Link Both Visuals: Ensure both the table and matrix visuals are using the same data model. This way, when you sort one visual, it will not affect the sorting of the other.
Use Bookmarks: To maintain the sort order independently on each visual, you can use bookmarks to save the state of your report page, including the sort order.
Sync Slicers: If you have slicers in your report, synchronize them across both visuals so that filtering on one visual will apply to the other.
By setting up your report this way, you can sort the table by any column while the matrix handles the monthly sales data. If you need to sort the matrix by other dimensions, consider adding those fields to the rows area and using the drill-down functionality. Remember, the sorting in the matrix will be limited to the hierarchy you set up in the rows area.