PBI aggregating rows incorrectly in Table visual despite "Don't summarize" selected

[lpriceFTW]

I have a Table visual which is just showing all fields from the underlying table. It will be useful for exporting filtered views to Excel.

The problem is PBI is aggregating rows with duplicate data together in the visual. Also, it is not summing the AMT column , so this aggregation gives a false idea of the total amount anyways.

I have all fields set to "Don't summarize" in the visual editor. I have fields set to "Don't summarize" in the table view. The data is flowing from a single table so modeling is not a factor.

Here is the visual which is aggregated incorrectly:

Here is the raw table data:

I cannot figure out what is going on.

Thanks.

[lbendlin · Accepted solution]

your rows are not unique. Add a primary key column (like an index) to your data AND to the visual.

Power BI visuals will ALWAYS aggregate/group by default.  That is different from the "Don't summarize" topic.

[Anonymous · Accepted solution]

Hi @lpriceFTW ,

Please try to add a new column, sunch as Index column, the key point is this column value should unique:

Table:

Table visual:

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @lpriceFTW ,

Please try to add a new column, sunch as Index column, the key point is this column value should unique:

Table:

Table visual:

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lpriceFTW · Accepted solution]

Hi @Anonymous,

I ended up doing this and it did fix the visual.

I still think this is very poor designing on Microsoft's part. My main complaint with PBI forcing aggregation, is that it is very difficult in the design stage to mess around with PowerQuery. This dataset contains 260,000,000 rows so it took almost all day to refresh the query after the index column was added. Would be so much simpler if Microsoft allowed users to bypass this.

My other issue I had was that PBI was not summing the aggregated AMT field. Do you know why this is? Even when I changed the AMT field in the table visual, it was still showing a single value rather than the sum of all aggregated values.

Thanks,

Logan

[lbendlin · Accepted solution]

your rows are not unique. Add a primary key column (like an index) to your data AND to the visual.

Power BI visuals will ALWAYS aggregate/group by default.  That is different from the "Don't summarize" topic.

[lpriceFTW]

Hi @lbendlin,

I added an index column and it fixed the visual.

My other issue I had was that PBI was not summing the aggregated AMT field. Do you know why this is? Even when I changed the AMT field in the table visual, it was still showing a single value rather than the sum of all aggregated values.

Thanks,

Logan

[lbendlin]

So you want to show individual rows, and you want to show aggregations?  These requirements conflict with each other.  You can use measures to resolve that conflict.

[lpriceFTW]

My reply was poorly worded.

I was trying to say that when PBI was forcing the aggregation, I tried to change the AMT field to sum, so that when it grouped all rows together, I could see the total dollar amount of all rows within the aggregation, rather than the dollar amount representative of one individual row.

That was a separate issue from the one where I was trying to remove the aggregation and show each row individually. I was able to show each row individually by adding the index column. When each row is shown individually, I do not need to sum them because each individual record in the AMT field is being shown and can be exported and calculated as I need it to.