Question

Why is my CSV data being aggregated automatically when visualized, and how can I disable that?
I’m importing data from a CSV file into Power BI, but the data is being aggregated automatically when visualized. I need to understand why this happens and how I can disable automatic aggregation to show the raw data instead.

Answer 1

When you import data from a CSV file into Power BI, it might automatically aggregate the data when you use it in visuals, especially if the fields are numeric. Power BI does this to provide a summarized view by default, which is why you may see aggregated values like sums, averages, or counts.

Reasons Why Data is Being Aggregated Automatically:

1. Numeric Fields:

   • Power BI automatically applies aggregation to numeric fields when they are added to a visual. This is because Power BI assumes you want to perform aggregation on numbers, such as summing, averaging, or counting the values.

2. Field Type:

   • If a field is recognized as a numeric or fact table column, Power BI might apply a default aggregation like "Sum" when it’s placed in the Values section of the visual.

3. Default Behavior:

   • When you drag fields into visuals, Power BI often aggregates them to ensure that the data is presented in a summarized way (e.g., showing total sales per category).

How to Disable Automatic Aggregation and Show Raw Data:

To stop Power BI from aggregating your data and show the raw data instead, follow these steps:

1. Change the Aggregation Type:

   • After adding a field to a visual, if Power BI aggregates the data, click the dropdown next to the field in the Values field well.

   • Select Don't summarize to prevent automatic aggregation.

2. Use Fields in the Axis or Legend:

   • If you want to display the raw data without aggregation, place the field in the Axis or Legend section of your visual instead of the Values section. Power BI won’t apply any aggregation to these fields.

   • For example, place a Category or Product field in the Axis or Rows section, and then place a Value field in the Values section without aggregation.

3. Adjust Data Model:

   • If you have imported the data into the model and want to ensure that Power BI doesn’t aggregate any columns by default, go to the Model view and inspect your table's properties. You can set each column's data type or define measures manually if you need more control over the aggregation process.

4. Use Explicit Measures:

   • If your raw data involves more complex calculations or custom aggregations, consider creating explicit DAX measures instead of relying on default aggregation. This allows you to control exactly how the data should be visualized.

5. Disable Auto Summarization (Optional):

   • In Power BI Desktop, you can turn off auto summarization for numeric fields. To do this, go to File > Options and settings > Options, and under Global > Data Load, uncheck the box for Auto Summarize. This stops Power BI from automatically summarizing numeric fields.