Export Data with Current Layout does not work when measures are on rows

[ashishd]

Hello,

I have a Power BI Matrix visual where multiple measures are on the rows (using Values->Options->Switch values to rows). There is no dimension on the rows. There are 2 dimensions on the columns to create the cross-tab view.

On the Power BI Service, when the Export data option for the visual is selected to get the data in the exact same format and layout in an excel, it does not work. The export moves the measures to columns and dimensions to rows.

Is there any way to make this work correctly?

Power bi file link:
https://drive.google.com/file/d/1nWyYexpzvJs_0YI4HxowW8RRQoTTF-fk/view?usp=sharing,

and exported excel link:

https://docs.google.com/spreadsheets/d/1v_JFeIooZtAfT2EYdbzzVr4WOQvHF8uY/edit?usp=sharing&ouid=10282...

Thank you!

[SolomonovAnton · Accepted solution]

Currently, Power BI does not support exporting matrix visuals with "Show values on rows" in the same layout from the Power BI Service to Excel. This is a known limitation. When exporting data from a matrix visual, Power BI flattens the data into a tabular format, placing:

• Measures as columns (regardless of whether "Values on rows" is enabled).
• Dimensions as rows.

This behavior causes the exported data to differ significantly from how it's visually presented in Power BI when "Values on rows" is turned on.

💡 Workarounds

1. Use Paginated Reports via Power BI Report Builder:
   • Paginated reports support exporting exactly what is rendered on the screen.
   • They are ideal for printing/exporting formatted tables and matrices to Excel or PDF.
   • Learn more about Report Builder
2. Recreate the Matrix layout manually in Excel using a DAX table:
   • Create a summary table using UNION and SELECTCOLUMNS in DAX to simulate the layout.
   • This allows you to flatten the matrix into a table and export it as desired.

🛠 Example of manual summary table using DAX:

SummaryTable =
UNION(
    SELECTCOLUMNS(YourTable, 
        "Measure", "Sales",
        "Value", [Total Sales],
        "Dim1", YourTable[Dim1],
        "Dim2", YourTable[Dim2]
    ),
    SELECTCOLUMNS(YourTable, 
        "Measure", "Profit",
        "Value", [Total Profit],
        "Dim1", YourTable[Dim1],
        "Dim2", YourTable[Dim2]
    )
)

Then create a table visual with this result and export it.

If you want the export to match the matrix layout perfectly, Paginated Reports remain your best option.

✔️ If my message helped solve your issue, please mark it as Resolved!

👍 If it was helpful, consider giving it a Kudos!

[ashishd]

Great! Thank you for your reply and clarifying my doubt.