• Resources
  • Jobs
  • Blog
  • Contact
  • 415.538.8484

DesignMind

Big Data, Business Intelligence, and Data Analytics Consultants

 
  • Company
    • Meet the DesignMind Team
    • Partners
    • Subscribe
    • Resources
    • Datasheets
    • White Papers
  • Services
    • Big Data Consulting
    • Business Intelligence & Data Warehousing
    • Cloudera Integration
    • Data Science
    • SQL Server and Database Services
    • Software Development
    • Technical Staffing
  • Videos
  • Meetups
  • Clients
December 15, 2020Chennie Teng

How To Export Underlying Data in Power BI

In order to make data visualizations even more useful, you need to learn how to export underlying data in Power BI. When you publish a report to your workspace, and want to export the underlying data from a table visualization, you may see the underlying data option greyed out and only see the summarized data.  When you hover on top of the underlying data, it will say “You must first add an aggregate to your chart to export the underlying data.”

Understanding Summarized Data and Underlying Data

Understanding summarized data

Before you try to export the underlying data, it’s important to understand the differences between summarized data and underlying data. Summarized exportation will only export aggregated data points in the visualizations; on the other hand, underlying data will export additional data that are not displayed in the visualization but are relevant to the visual.

When to export Summarized Data vs When to export Underlying Data

You should export summarized data when you want to see the relevant data in the visualization. Summarized data will only show the columns and measures that you created for the visualizations. If the visual contains aggregation, the export will contain aggregated data.

You should export underlying data in power bi when you want to see the relevant data and additional data from the data model you have created in Power BI. However, if your visual contains an aggregation, it will not be exporting the aggregated data.

Both data will be exported to an xlsx file, and your internet browser will prompt you to save the file, and then open it in Excel.

How to export Underlying data in Power BI

First, you have to make sure you have the correct settings. In Power BI Desktop, please select File > Options and Settings > Options > Current File > Report Settings, then under Export Data subtitle, please enable “Allow End Users to Export Both Summarized and Underlying Data from the Service.”

Export both summarized and underlying data

If the first step worked for you, congratulations. If the first step did not work for you, and the underlying data option is still greyed out, you need to create an aggregate function and implement it in the Power BI Visual. This can be achieved by creating a Power BI calculated measure and using any aggregate function, and then importing it inside the visualization. I like to use the SUM function, but functions like MIN, MAX, AVERAGE work too.

use any aggregate function

Remember, the function does not have to make sense; you are simply creating the function to bypass the export blockage in the Power BI Service. After you import the aggregate function inside of the data visualization, you can hide the aggregate function by minimizing the column. After it is complete, you can publish the report again.

publish the report again and now you can export underlying data in power bi

Congratulations, you are now able to export both summarized and underlying data from the Power BI Service!  You can learn more about Power BI on our Power BI and Data Visualization page.

Chennie Teng is a BI Consultant at DesignMind. 

Recent Posts

How To Export Underlying Data in Power BI

December 15, 2020

Right Sizing Your Power BI Gateway on AWS

May 26, 2020

Creating Power BI Custom Visuals

April 21, 2020

My Power BI report is too slow. What tools and techniques should I use?

March 2, 2020

Are Your Power BI Performance Issues Due To High Memory Consumption?

January 17, 2020

Power BI Parameters – How to Use & Update Parameters in the Power BI Service

December 9, 2019

Power BI Tips: How To Increase The Number of Rows Exported to Excel

September 6, 2019

Power BI Data Modeling Tips for Developers and Analysts

October 15, 2018

Where is Microsoft taking Power BI?

July 25, 2018

Company

  • About
  • Team
  • Clients
  • Partners
  • Privacy and Cookie Policy

Resources

  • Blog
  • Resources
  • White Papers
  • Datasheets

Community

  • Microsoft Technical Groups
  • Bay Area BI User Group
  • San Francisco Data Platform
  • Silicon Valley Data Platform
  • San Francisco Power BI
  • Twitter
  • LinkedIn
  • Subscribe
  • Contact

© 2021 DesignMind. All rights reserved.