Table & Matrix Combo

[meiwah]

Hi,

I would like to have a table which can show for every product – Country/Region, City, Category, Sub-Category and Product Name and the Sales of every month. For the portion highlighted by the red box, can be satisfied by a table. For the portion highlighted by the orange box, can be satisfied by a matrix.

I can’t use a matrix with step off because I need to sort the table not only by the first column – Country/Region but also by City, Category, Sub-Category and Product Name as what can be done in a table.

Is there anyone who know how to implement such a table and matrix combo? Pls help! I’ve tried Deneb but maybe I’m not good at it, I didn’t manage to find the solution. Thanks! 🙂

[mahenkj2]

Hi @meiwah ,

---

I can’t use a matrix with step off because I need to sort the table not only by the first column – Country/Region but also by City, Category, Sub-Category and Product Name as what can be done in a table.

---

Perhaps I am mistaken to understand your problem correctly, but if not so, can you not individually sort this way?

 Please check if it helps.

[meiwah]

Hi Mahenkj, 

Thanks for your reply! The sorting I would like to have is like in a table where if the sorting is done by country, the table matrix combo should yield the below order. 

Thanks and really appreciate your effort! 🙂

[meiwah]

Hi Mahenkj, 

Thanks for your reply! The sorting I would like to have is like in a table where if the sorting is done by country, the table matrix combo should yield the below order. 

Thanks and really appreciate your effort! 🙂

[AnalyticsWizard]

@meiwah 

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:

1. 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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.

[meiwah]

Hi AnalyticsWizard,

If I've the data in a table and a matrix separately, it will inititally appears as the below

But when subsequently, the table is sorted by Category in descending order, the order of the products will change in the table but the product order in the matrix wouldn't be able to follow

May be I didn't understand your solution fully, pls enlightening on how to sync the sort order between the table and the matrix. Thank you very much! 🙂

[meiwah]

Hi AnalyticsWizard,

If I've the data in a table and a matrix separately, it will inititally appears as the below

But when subsequently, the table is sorted by Category in descending order, the order of the products will change in the table but the product order in the matrix wouldn't be able to follow

May be I didn't understand your solution fully, pls enlightening on how to sync the sort order between the table and the matrix. Thank you very much! 🙂

[Ritaf1983]

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://inforiver.com/matrix/

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

[meiwah]

Thanks! I've already voted for the idea

[Ashish_Mathur]

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.

[lbendlin]

I can’t use a matrix with step off because I need to sort the table not only by the first column – Country/Region but also by City, Category, Sub-Category and Product Name

sort how? alphabetically?