Forum Discussion

ashishd's avatar
ashishd
Advocate II
1 year ago
Solved

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

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=102820958532213704543&rtpof=true&sd=true

 

Thank you!

Need Help
  • SolomonovAnton's avatar
    SolomonovAnton
    1 year ago

    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!

3 Replies

  • SolomonovAnton's avatar
    SolomonovAnton
    Super User
    1 year ago

    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's avatar
      ashishd
      Advocate II
      1 year ago

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

    • PBIdashboards's avatar
      PBIdashboards
      Post Patron
      1 month ago

      The accepted solution is correct  "Show values on rows" doesn't preserve layout on export, and Paginated Reports is the proper fix for pixel-perfect exports.

      Worth adding one more option for teams where Finance users need the matrix layout to work in both the report AND the export without building a separate Paginated Report: Flexa Tables on AppSource exports data in the visual's layout  rows stay rows, columns stay columns, including when measures are displayed on rows. No Paginated Reports setup needed