Visual Export from Desktop is Correct but Export from Service is Wrong

[caw1991staffs]

I have a stacked column chart visual. Its x-axis shows delivery locations. The y-axis shows count of order IDs. They are stacked by whether the delivery was on time using a Y/N flag. All data in the visual is from the same dataset. There are no measures being used in the visual.

In Power BI Desktop when I select the three dots on the visual to export the data to Excel, I get the below, which is correct and what I expect:

However when I navigate to the Power BI workspace in which the report is published and attempt to export the same thing, selecting summarised data, I receive the below:

I don't understand why this is happening and it's frustrating as desktop is giving me exactly what I want!

Can anyone please advise the cause and a fix?

Thank you

[V-yubandi-msft · Accepted solution]

Hi @caw1991staffs ,

Thanks for reaching out about this issue. We investigated the behavior you mentioned when exporting summarized data from the Power BI Service, where exporting to Excel shows a live connection message instead of the expected summary data seen in Power BI Desktop.

After testing, we found that this is intentional in some cases. When exporting to Excel, especially from visuals like stacked column charts, Power BI might create a live link to the dataset instead of exporting the summarized rows. This is more likely with models that have row level security, large datasets, or specific tenant settings. Since the Excel file uses a live connection, it may not display any data unless refreshed in Excel, and access permissions can still prevent it from loading correctly.

To export summarized data from a visual in Power BI Service, click the three dots in the top-right corner of the visual, then select  Export data. In the dialog that appears, choose the .CSV  format and make sure  Summarized data  is selected before clicking  Export to download the file.

FYI:

When Selecting live connection

When Selecting .CSV

This will provide a flat CSV file with the actual summarized values (like DestinationAddressCode, OnTime, Order Count, etc.), similar to what you expect from Power BI Desktop.

 If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[caw1991staffs]

Thank you that's now returning exactly what I expected!

[Nasif_Azam]

Hey @caw1991staffs ,

It seems that you're encountering a discrepancy between the data export behavior from Power BI Desktop and the Power BI Service. When exporting data from Power BI, the behavior differs between Desktop and Service. In Power BI Desktop, the export includes all fields, aggregations, and formatting as seen in the visual. In contrast, the Power BI Service export is based on summarized data, which may not capture the same level of detail or formatting, potentially leading to discrepancies in the exported data. The "Summarized data" export option in the Service aggregates data based on what's displayed in the visual, which may cause mismatches if the aggregation differs from the Desktop version.

Solutions:

1. Check Aggregation Settings: Double-check the aggregation or transformation settings in the Power BI Service. Ensure that the visual's underlying data model isn't being altered in a way that would impact the export.

2. Export Raw Data: Instead of selecting "Summarized Data" for export in the Power BI Service, try using the "Underlying Data" option to export all data without any aggregation.

3. Re-publish Dataset: Sometimes, refreshing the dataset in the Power BI Service or re-publishing the report can help synchronize the behavior between Desktop and Service.

4. Check Permissions: Ensure that the permissions for data export in the Power BI Service allow users to access the data in the same way they can in Desktop. Sometimes, restrictions in the Service could affect how data is exported.

For Detailed Information:

Export Data in Power BI Desktop

Exporting Data from Power BI Service

Underlying Data vs Summarized Data in Power BI

Power BI Data Export Limitations

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam