Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Viswanath Iyer]

Hi,

I had a couple of queries with regards to the Matrix and Table visualizations:

MATRIX VISUALIZATION
Q: When I export results into excel, I do not see the data in the same Matrix form as it was in Power BI. Is there any way to get the desired format?

TABLE VISUALIZATION
Q: Let’s say I have Category, Sub-Category and Sales details in the Table. I want to ensure that my Sub-Categories are all shown together one below and sort the Sales amount. I would also like to retain the same look and order even if someone removes the Category column. Is it possible?

——————————
Vishy
BI Consultant
——————————

[John Thomas]

Vishy,

Matrix Visual Export:
The matrix visual is just like a pivot table in Excel.  The underlying data is not it the matrixed format, but rather just “organized” like a pivot table.  If you want it like a pivot table in Excel, export out the data, then create a pivot table to re-create the format you want to look like.  I have to ask however, why export it at all?  Just look at in as a published report.  The whole point behind Power BI is to create Reports that provide actionable data to your user community.  If they want it in Excel so they can look at the same report you have in Power BI, it kind of defeats the purpose doesn’t it?  Or perhaps your report in Power BI needs to be changed?

Table Visual:
This is rather easy to do if I understand your question.  Just to recap, you want “Sub-Category” sorted in the same way even if you remove “Category” from the visual.  To do this go back to your query you used to develop or import your Sub-Category values.  The ideal situation is this comes from a “raw data” table that is not loaded into the data model, but has all of your initial ETL data.  From here you can build a unique data set for your Sub-Category AND ensure that all Sub-Categories in your fact table are also in the dimension Sub-Category table.  Next step would be to add an Index column (Int64.Type) that is in the order you want the sub-Categories to be in.  Then back in Data view of your tables, click on the “Sub-Category” field so it’s highlighted, then click on the “Sort by column” button in the “Column tools” menu group and select your Index column.  This will ensure that whenever you have “Sub-Category” on a visual, it will sort it by the index value and not the “Sub-Category” values.  If you want to post your pbix file and some sample data I could give an example.

——————————
John Thomas
Huntersville NC
——————————
——————————————-